CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlarel
Diego Castillo
Diego Castillo

Reputation: 355

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

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!

Upvotes: 2

Views: 258

Answers (1)

3limin4t0r
3limin4t0r

Reputation: 21130

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

Upvotes: 2

Related Questions