CODEWITHSUNDEEP
ruby-on-railspostgresql
tonic
tonic

Reputation: 103

Rails order / sort dates based on two different dates

Each record contains two fields - field1 and field2. Both contain dates.

For reach record: 1 field might have been filled in, for another it might be 2 of the fields or none at all.

I'd like to in the most efficient way possible order them based on this.

So it would compare field1 and field2 for a record and find which one has the latest date. It would then list all of the records in order based on this and then display the records that don't have any kind of entry.

I had it sorted for just field1 like this:

order("CASE WHEN field1 IS NULL THEN 1 ELSE 0 END, field1 desc"

But when I try and introduce field 2 into the query it doesn't give me the results that I would expect.

Any ideas?

Upvotes: 0

Views: 528

Answers (2)

Marth
Marth

Reputation: 24802

Order by GREATEST of the dates, and add NULLS LAST to have rows with both dates set to NULL last:

[local] #= CREATE TEMP TABLE t (d1 date, d2 date, id serial);
CREATE TABLE
Time: 25,122 ms

[local] #= INSERT INTO t VALUES 
    (now(), now() - '1 day'::interval),
    (now(), NULL),
    (NULL, now() - '1 day'::interval),
    (NULL, NULL)
;
INSERT 0 4
Time: 1,194 ms

[local] #= SELECT * FROM t;
┌────────────┬────────────┬────┐
│     d1     │     d2     │ id │
├────────────┼────────────┼────┤
│ 2016-06-23 │ 2016-06-22 │  1 │
│ 2016-06-23 │ (null)     │  2 │
│ (null)     │ 2016-06-22 │  3 │
│ (null)     │ (null)     │  4 │
└────────────┴────────────┴────┘
(4 rows)

[local] #= SELECT * FROM t ORDER BY GREATEST(d1, d2) DESC NULLS LAST;
┌────────────┬────────────┬────┐
│     d1     │     d2     │ id │
├────────────┼────────────┼────┤
│ 2016-06-23 │ 2016-06-22 │  1 │
│ 2016-06-23 │ (null)     │  2 │
│ (null)     │ 2016-06-22 │  3 │
│ (null)     │ (null)     │  4 │
└────────────┴────────────┴────┘
(4 rows)

Upvotes: 1

MageeWorld
MageeWorld

Reputation: 402

My understanding of SQL queries in general is that the sorting will be one, then two, you can't sort by one field than sort by another.

The way I'd do this involves creating a 3rd column (not really that hard) in your database column, calling sort_date. Write an after_update method that compares date1 and date2 and sets sort_date based on the comparison condition you want. (Also a default value for sort_date might be necessary depending on other code). Then just re-write your query to sort by sort_date

Upvotes: 0

Related Questions