Note that the output has been "stylized" so it reads better here on SO.
What I've got...
(sql/format
(->
(sqlh/select :*)
(sqlh/from :event)
(sqlh/merge-where [:in :field_id ["1673576", "1945627", "1338971"]])
(sqlh/merge-where [:in :layer ["fha.abs" "fha.rank" "fha.true-color"]])
(sqlh/merge-order-by :field_id)
(sqlh/merge-order-by :layer)
(sqlh/merge-order-by :event_date)
(sqlh/limit 5)))
=>
["SELECT *
FROM event
WHERE ((field_id in (?, ?, ?)) AND (layer in (?, ?, ?)))
ORDER BY field_id, layer, event_date
LIMIT ?"
"1673576"
"1945627"
"1338971"
"fha.abs"
"fha.rank"
"fha.true-color"
5]
What I want...
(sql/format
(->
(sqlh/select :*)
(sqlh/from :event)
(sqlh/merge-where [:in :field_id ["1673576", "1945627", "1338971"]])
(sqlh/merge-where [:in :layer ["fha.abs" "fha.rank" "fha.true-color"]])
;;; this doesn't work, but is conceptually what I'm looking for
(sqlh/merge-order-by [:field_id :layer :event_date])
(sqlh/limit 5)))
=>
["SELECT *
FROM event
WHERE ((field_id in (?, ?, ?)) AND (layer in (?, ?, ?)))
ORDER BY (field_id, layer, event_date)
LIMIT ?"
"1673576"
"1945627"
"1338971"
"fha.abs"
"fha.rank"
"fha.true-color"
5]
How can I get HoneySQL to emit SQL that treats my order by clause as the compound key that the table itself is using as the Primary Key?
It seems HoneySQL should be able to do this as it "does the right thing" when presented the same challenge in a where clause like...
(sql/format
(->
(sqlh/select :*)
(sqlh/from :event)
(sqlh/merge-where [:= [:field_id :layer :event_date] ["1338971" "fha.abs" (c/from-string "2011-08-02T10:54:55-07")]])))
=>
["SELECT * FROM event WHERE (field_id, layer, event_date) = (?, ?, ?)"
"1338971"
"fha.abs"
#object[org.joda.time.DateTime 0xe59f807 "2011-08-02T17:54:55.000Z"]]
First you need to look at the format behavior on order-by
(sql/format {:order-by [:c1 :c2]})
=> ["ORDER BY c1, c2"]
(sql/format {:order-by [[:c1 :desc] :c2]})
=> ["ORDER BY c1 DESC, c2"]
that is the struct about order-by which will be generated.
If you look at the macro defhelper
it will do two things.
(do
(defmethod
build-clause
:order-by
[_ m fields]
(assoc m :order-by (collify fields)))
(defn order-by [& args__14903__auto__]
(let [[m__14904__auto__ args__14903__auto__] (if
(plain-map?
(first
args__14903__auto__))
[(first
args__14903__auto__)
(rest
args__14903__auto__)]
[{}
args__14903__auto__])]
(build-clause :order-by m__14904__auto__ args__14903__auto__)))
(alter-meta! #'order-by assoc :arglists '([fields] [m fields])))
The collify
is very simple.
(defn collify [x]
(if (coll? x) x [x]))
So , we need to look at defn order-by
function .
When you call (sqlh/merge-order-by {} [:a :b])
,
args__14903__auto__ = '({} [:a :b])
The first if
will create two var m__14904__auto__ = {}
and args__14903__auto__ = (rest args__14903__auto__) = ([:a :b])
.
So, I guess the merge-order-by function is wrong.
I solve your problem like this.
(sql/format
(->
(sqlh/select :*)
(sqlh/from :event)
(sqlh/merge-where [:in :field_id ["1673576", "1945627", "1338971"]])
(sqlh/merge-where [:in :layer ["fha.abs" "fha.rank" "fha.true-color"]])
(sqlh/merge-order-by [:field_id :desc] :layer :event_date)
(sqlh/limit 5)))