CODEWITHSUNDEEP
sqlruby-on-railsrubyrails-activerecord
cvdv
cvdv

Reputation: 2932

How do I order table columns by the most recent date?

I have email_invitations table with three datetime columns, sent_at, opened_at, and rsvped_at. How do I sort the three columns by their date in ascending order (most recent at top)?

I'm using default_scope in rails:

default_scope { order('sent_at ASC' }

Here's what I have 

EmailInvitation
id: 1, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-03-25"
id: 2, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-04-01"
id: 3, sent_at: "2016-03-23", opened_at: "2016-03-24", rsvped_at: nil
id: 4, sent_at: "2016-03-29", opened_at: nil, rsvped_at: nil

Using EmailInvitation.order(sent_at: :asc, opened_at: :asc, rspved_at: :asc) it looks like it sorts sent_at first, then opened_at, then rsvped_at. 

id: 4, sent_at: "2016-03-29", opened_at: nil, rsvped_at: nil
id: 1, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-03-25"
id: 2, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-04-01"
id: 3, sent_at: "2016-03-23", opened_at: "2016-03-24", rsvped_at: nil

but what I really want whatever has the most recent date will be at the top so somehow I need to join all the columns so it doesn't look at the column name, just the date.

id: 2, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-04-01"
id: 4, sent_at: "2016-03-29", opened_at: nil, rsvped_at: nil
id: 1, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-03-25"
id: 3, sent_at: "2016-03-23", opened_at: "2016-03-24", rsvped_at: nil

Upvotes: 1

Views: 169

Answers (1)

mu is too short
mu is too short

Reputation: 434665

Presumably:

  1. sent_at will never be nil.
  2. If opened_at is nil then so is rsvped_at.
  3. sent_at will always be at most opened_at or opened_at will be nil.
  4. If opened_at is not nil then it will always be at most rsvped_at or rsvped_at will be nil.

That means that you're really trying to sort by the first non-nil entry in

[rsvped_at, opened_at, sent_at]

Right? That's that same as trying to pick the first non-NULL entry in that list and sorting by that. That's pretty simple to express using COALESCE:

order('coalesce(rsvped_at, opened_at, sent_at) desc')

The SQL COALESCE function simply returns the first (from left to right) of its arguments that is not NULL.

Upvotes: 2

Related Questions