Is it possible to use case expression in order-by?

[vharmain]

I would like to be able to sort by different fields based on a condition. This is supported at least in Postgresql.

SELECT *
FROM my_table
ORDER BY CASE WHEN field_x = 1 THEN field_y ELSE field_z END

I tried to express this in HoneySQL V2:

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

..which yields weird results

=> ["SELECT * FROM my_table ORDER BY case ASC, = FIELD_X, field_y ASC, else ASC, field_z ASC"]

From the docs I can read that :order-by accepts a sequence of one or more ordering expressions. but apparently I want to use a "normal expression" instead of an "ordering expression" in this case.

Is there a workaround how this can be achieved without falling back to raw SQL?

[seancorfield]

It's like :select, you need extra nesting:

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=>

Because :order-by takes a sequence of order expressions, and an order expression can be:

• a column name (implicitly :ascending)
• a pair of column name and direction (:asc or :desc)
• a pair of an expression and (optional) direction

I'll update the docs to make that clearer.