Rails order / sort dates based on two different dates

Question

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?

Marth · Accepted Answer · 2016-06-23 14:55:08Z

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)

MageeWorld · Accepted Answer · 2016-06-23 14:53:24Z

0

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

edited Jun 23, 2016 at 14:53

answered Jun 23, 2016 at 14:50

MageeWorld

4024 silver badges14 bronze badges

1 Comment

Edvaldo Silva de Almeida Jr Over a year ago

What kind of answer is this? Delete it before you get downvoted.

Collectives™ on Stack Overflow

Rails order / sort dates based on two different dates

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related