如何使HoneySQL通过复合键来处理订单？ [英] How can I make HoneySQL handle order by as a compound key?

问题描述

请注意，输出已经风格化，因此在SO上读起来更好。

Note that the output has been "stylized" so it reads better here on SO.

我所拥有的...

(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发出SQL来将我的order by子句视为表本身用作主键的复合键？

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?

似乎HoneySQL应该能够做到这一点，因为当呈现相同的键时，它做对了在where子句之类的挑战中...

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

推荐答案

首先，您需要查看排序方式

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

这是关于order-by的结构

that is the struct about order-by which will be generated.

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

If you look at the macro defhelper it will do two things.

1. 为规范类型定义记录


2. 定义一个调用mutimethod的函数


（do
（defmethod
build-clause
：order- by
[_ m个字段]
（关联m：order-by（整理字段）））
（defn order-by [& args__14903__auto__]
（让[[m_14904__auto__ args__14903__auto __]（如果
（普通地图？
（第一个
args__14903__auto__））
[（第一个
args__14903__auto__）
（其余
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]）时，

So , we need to look at defn order-by function . When you call (sqlh/merge-order-by {} [:a :b]),

args__14903__auto__ ='（{} [：a：b]）

第一个 if 将创建两个var m__14904__auto__ = {} 和 args__14903__auto__ =（rest args__14903__auto__）=（[:: 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)))

这篇关于如何使HoneySQL通过复合键来处理订单？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！