如何将HoneySQL句柄作为复合键进行处理？

Question

请注意，输出已经被“样式化”，因此在这里读起来更好。

我所拥有的..。

(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]

我想要的..。

(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]

如何使HoneySQL发出将order子句视为表本身用作主键的复合键的SQL？

似乎HoneySQL应该能够做到这一点，就像它在where子句中提出同样的挑战时“做正确的事情”一样。

(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"]]

Answer 1

首先，您需要查看order-by上的格式行为

(sql/format {:order-by [:c1 :c2]}) 
=> ["ORDER BY c1, c2"]
(sql/format {:order-by [[:c1 :desc] :c2]})
=> ["ORDER BY c1 DESC, c2"]

这就是关于秩序的结构，它将由此产生。

如果您查看宏defhelper，它将做两件事。

1. 规范类型的错误记录
2. 定义一个函数来调用mutimethod

(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])))

collify非常简单。

 (defn collify [x]
     (if (coll? x) x [x]))

所以，我们需要看看defn order-by函数。当你打电话给(sqlh/merge-order-by {} [:a :b])时，

args__14903__auto__ = '({} [:a :b])

第一个if将创建两个var m__14904__auto__ = {}和args__14903__auto__ = (rest args__14903__auto__) = ([:a :b])。

所以，我想合并顺序函数是错误的。

我就这样解决你的问题。

(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)))

Answer 2

两年多了，但我终于了解了足够多的Clojure，并与HoneySQL合作了足够长的时间来了解我年轻的自我所缺少的东西。它不希望复合键作为向量：sqlh/order-by是可变的：

(doc sqlh/order-by)
-------------------------
honeysql.helpers/order-by
([& fields] [m & fields])
  nil

它真正想要的是(sqlh/order-by :field_id :layer [:event_date :desc])。该向量只需要进行特定的字段排序DESC。

所以这就是我想要做的

(-> (sqlh/select :*)
    (sqlh/from :event)
    (sqlh/merge-where [:in :field_id field-ids])
    (sqlh/merge-where (cond (not-empty layers) [:in :layer layers]))
    (sqlh/merge-where (make-where-for-timestamp
                        :event_date event-date-from event-date-to))
    (sqlh/merge-where (make-where-for-timestamp
                        :updated_at updated-at-from updated-at-to))
    (sqlh/order-by :field_id :layer [:event_date :desc])
    sql/format)
=>
["SELECT * FROM event WHERE ((field_id in (?)) AND (layer in (?, ?, ?))) ORDER BY field_id, layer, event_date DESC"
 "1325629"
 "fha.true-color"
 "fha.abs"
 "fha.rank"]

@救世主在https://stackoverflow.com/a/40356529/688355一直都是正确的。我的Clojure还不足以理解它。