Rails + Postgres: How to `order` Using Multiple Possibly Nil Columns?

Question

I have a table in a Rails application which stores two attributes, one of which is a timestamp (sent_at, as in Time.now), and the other a date (sent_on, as in Time.now.to_date).

I'd like to retrieve rows in ascending order by their sent_at|sent_on (either one of those could be nil):

Order.first(20).order(sent_at: :asc, sent_on: :asc) # this doesn't work

How would you go about it?

Thanks!

Answer 1

I've taken the liberty to use the comment of Islingre. You can use COALESCE to evaluate it's arguments in order. The first argument that is not NULL will be returned.

named_function = ::Arel::Nodes::NamedFunction
orders = Order.arel_table
Order.order(named_function.new('COALESCE', [orders[:sent_at], orders[:send_on]]).asc).first(20)

# results in:
#
# SELECT "orders".*
# FROM "orders"
# ORDER BY COALESCE("orders"."send_at", "orders"."send_on") ASC
# LIMIT 20

Or if you don't mind the use of plain SQL.

Order.order('COALESCE(sent_at, sent_on) ASC').first(20)

# results in:
#
# SELECT "orders".*
# FROM "orders"
# ORDER BY COALESCE(sent_at, sent_on) ASC
# LIMIT 20