Use `case` with `order`?

[nimmolo]

Hi, still enjoying this gem - thank you.

Currently I'm trying to use case in an order statement, but my syntax is not working.

Library.select(:code, :name).distinct.where(Library[:name].matches("foo%")).
order(Library[:code].when(nil).then(name: :asc).else(code: :asc, name: :asc))

I'm getting #<TypeError: can't quote Hash>

If this usage is possible, can you please provide a brief example?

[jdelporte]

Hi, Thank you for your support. I don't know any RDBMS where you can write something like that

SELECT code, name FROM libraries ORDER BY CASE WHEN code IS NULL THEN name ASC ELSE code ASC, name ASC END

CASE is an expression, it must return a value and not some order "predicate". Plus CASE code WHEN NULL ... will never be true due to NULL nature.

For what I understand, you can have what you ant by doing this :

Library.select(:code, :name).distinct.where(Library[:name].matches("foo%")).
order(
  Arel.when(Library[:code].is_null).then(Library[:name]).else(Library[:code]).asc,
  Library[:name].asc,
)

or

Library.select(:code, :name).distinct.where(Library[:name].matches("foo%")).
order(
  Arel.when(Library[:code].is_null).then('A').else(Arel.quoted('A').concat(Library[:code])).asc,
  Library[:name].asc,
)

or even easier (but you need to configure your DBMS to sort null first)

Library.select(:code, :name).distinct.where(Library[:name].matches("foo%")).
order(
  Library[:code].asc,
  Library[:name].asc,
)

[nimmolo]

Thank you @jdelporte ! 🙏