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
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.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 varm__14904__auto__ = {}
andargs__14903__auto__ = (rest args__14903__auto__) = ([:a :b])
.So, I guess the merge-order-by function is wrong.
I solve your problem like this.