HoneySQL can't deal with compound key in WHERE clause?

Question

I can't get HoneySQL to emit SQL that allows me to use a compound key in a WHERE clause.

What I'm trying to get: SQL:

SELECT field_id, layer, event_date, is_deleted
FROM event
WHERE 
  field_id in ('1325629', '1627236', '1673576') AND
  layer in ('fha.raw') AND
  (field_id,layer,event_date) > ('1627236','fha.raw', '2018-07-23 09:45:07.6-07')
ORDER BY field_id, layer, event_date;

HoneySQL:

(-> (sqlh/select :field_id :layer :event_date :is_deleted)
    (sqlh/from :event)
    (sqlh/merge-where [:in :field_id field-ids])
    (sqlh/merge-where (cond (not-empty layers) [:in :layer layers]))
    (sqlh/merge-where [:> 
                       [:field_id :layer :event_date] 
                       ["1627236" "fha.raw" (c/from-string "2018-07-23T09:45:07.6-07:00")]])
    (sqlh/order-by :field_id :layer :event_date)
    sql/format)

HoneySQL is producing something that can't work

["SELECT field_id, layer, event_date, is_deleted FROM event WHERE (((field_id in (?, ?, ?)) AND (layer in (?, ?, ?, ?))) AND field_id(layer, event_date) > 1627236(?, ?)) ORDER BY field_id, layer, event_date"
 "1325629"
 "1627236"
 "1673576"
 "fha.abs"
 "fha.rank"
 "fha.true-color"
 "fha.raw"
 "fha.raw"
 #object[org.joda.time.DateTime 0x4fa79ee8 "2018-07-23T16:45:07.600Z"]]

... it dorked up my compound key in the WHERE clause, mistakingly building a function call or something: field_id(layer, event_date) rather than (field_id, layer, event_date)

How do I make this work?

This is related to a question I asked earlier when I was trying to do the same in the ORDER BY clause (How can I make HoneySQL handle order by as a compound key?). It turns out that never really got resolved in terms of compound key, but did allow me to sort DESC on one of the fields. In other words, I've yet to find how to get HoneySQL to do a compound key in either the WHERE or ORDER BY clauses.

I could use a little direction.

Answer 1

As most of the SQL abstractions, HoneySQL works only for the standard cases. I believe it's a good moment to use a raw statement. It inserts an SQL statement as is without preprocessing it, either with a function or a tag:

(sql/raw "@var := foo.bar")
#sql/raw "@var := foo.bar"

In your case, it would be just:

...
(sqlh/merge-where
  #sql/raw "(field_id,layer,event_date) > ('1627236','fha.raw', '2018-07-23 09:45:07.6-07')")
...

and you're good.

Af course, never pass a user's input into a raw SQL statement.

Answer 2

You may want to consider Walkable, another SQL library with a more expressive language https://walkable.gitlab.io/s-expressions.html

For instance, this is your query translated to Walkable:

(let [field-ids     [1 2 3]
      layers        ["a" "b" "c"]
      layer-filters (when (not-empty layers) `([:in :layer ~@layers]))]
  `[(:event/all {:filters  [:and [:in :event/field-id ~@field-ids]
                            ~@layer-filters
                            [:> [:tuple :event/field-id :event/layer :event/event-date]
                             [:tuple "1627236" "fha.raw" "2018-07-23T09:45:07.6-07:00"]]]
                 :order-by [:event/field-id :event/layer :event/event-date]}
                [:event/field-id :event/layer :event/event-date :event/is-deleted])])