JOIN with COALESCE and ORDER BY in Rails

Question

I just ran into an issue with Rails where I couldn't properly use JOIN and multiple ORDER BYs (with one of them being a COALESCE function) in the same query. Is this something that is illegal in SQL (doubtful) or is it just an issue with Rails' implementation? Also, how can I get around it?

# Works!
Post.joins(:author).order("COALESCE(title, '---') DESC")
=> SELECT  "posts".* FROM "posts" INNER JOIN "authors" ON "posts"."author_id" = "authors"."id" ORDER BY COALESCE(title DESC, '--')

# Fails - syntax error in SQL
Post.joins(:author).order("COALESCE(title, '---') DESC").last #last should automatically apply an ORDER BY id DESC;
=> SELECT  "posts".* FROM "posts" INNER JOIN "authors" ON "posts"."author_id" = "authors"."id" ORDER BY COALESCE(title DESC, '--') ASC LIMIT 1

Answer 1

when applying order("COALESCE(table.column, fallback_value) asc/desc") to my query I started getting the following error

ActiveRecord::UnknownAttributeReference: Query method called with non-attribute argument(s):

this was resolved by wrapping the order argument with Arel.sql as suggested by this Answer

order(Arel.sql("COALESCE(table.column, fallback_value) asc/desc"))

For more information on why this works I found this article from Thoughtbot to be helpful.