Fork me on GitHub
#honeysql
<
2022-07-15
>
valtteri13:07:40

Hi! Is it possible to use case in order-by ? I tried this and it outputs weird results

(sql2/format {:select [:*]
                :from :my-table
                :order-by [:case [:= :a 1] :a :else :b]})

=> ["SELECT * FROM my_table ORDER BY case ASC, = A, a ASC, else ASC, b ASC"]
I also tried wrapping case to extra set of [] but that throws an exception that it's expecting a keyword.

valtteri13:07:18

I'm trying to sort conditionally by different fields. This is supported at least in postgres

valtteri13:07:22

Maybe the question is how do I tell that case is an expression and not a column :thinking_face:

seancorfield14:07:59

I'm on vacation today but if you create an issue on GH with details, I'll try to take a look this weekend and either add a solution to the issues (and docs) or turn it into an enhancement to be added.

valtteri15:07:01

Thanks Sean! I created an issue https://github.com/seancorfield/honeysql/issues/414 Have a nice vacation! 😎

seancorfield20:07:33

Just in case anyone needs to know the answer, without having to read the GH issue:

user=> (sql/format {:select [:*]
  #_=>              :from :my-table
  #_=>              :order-by [[[:case [:= :field_x 1] :field_y :else :field_z]]]})
["SELECT * FROM my_table ORDER BY CASE WHEN field_x = ? THEN field_y ELSE field_z END ASC" 1]
user=>
and I'm updating the docs to include that example for :order-by

👍 1
1
seancorfield20:07:33

Just in case anyone needs to know the answer, without having to read the GH issue:

user=> (sql/format {:select [:*]
  #_=>              :from :my-table
  #_=>              :order-by [[[:case [:= :field_x 1] :field_y :else :field_z]]]})
["SELECT * FROM my_table ORDER BY CASE WHEN field_x = ? THEN field_y ELSE field_z END ASC" 1]
user=>
and I'm updating the docs to include that example for :order-by

👍 1
1