clojure - 如何让 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 by 子句视为表本身用作主键的复合键的 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"]

这是将要生成的关于order-by的结构。

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

1. 规范类型的defrecord
2. 定义一个函数来调用 mutimethod


(做
(定义方法
构建子句
:order-by
[_m 字段]
(assoc m :order-by (collify fields)))
(定义顺序 [& args__14903__auto__]
(让 [[m__14904__auto__ args__14903__auto__](如果
(普通 map ？
(第一的
args__14903__auto__))
[(第一的
args__14903__auto__)
(休息
args__14903__auto__)]
[{}
args__14903__auto__])]
(构建子句:按 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]).

所以，我猜 merge-order-by 函数是错误的。

我这样解决你的问题。

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