CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlsortingactiverecord
ConstantMalachi
ConstantMalachi

Reputation: 53

PostgreSQL in Rails: sorting object by two date attributes in descending order

I have an index of active job positions. Currently, they're sorted by the most recent i.e. created_at. However, recently i've added in a renewal feature that updates a renewal_date attribute without updating the created_at.

What I want to achieve is to sort the list in descending order using both renewal_date and created_at.

jobs = Job.where(active: true).reorder("renewal_date DESC NULLS LAST", "created_at DESC")

With this code, the renewed job will always be at the top regardless of how many new jobs are created. How do I sort it so it checks for the date for both attributes and sorts it according to most recent?

Upvotes: 1

Views: 377

Answers (2)

Hieu Pham
Hieu Pham

Reputation: 6707

Let try a standard SQL, so it can work with all types of database:

Job.where(active: true).order('CASE WHEN renewal_date IS NULL THEN created_at ELSE renewal_date END DESC')

Upvotes: 2

gvo
gvo

Reputation: 855

Your code will order first by renewal_date with nulls at the end, and then will look at the created_at if two records have the same renewal_date.

I assume that what you want to do is something like "max(renewal_date, created_at)", which will take the "last modification date", or another custom way to compare the two fields. If then, you can find your answer here : merge and order two columns in the same model

Job.where(active: true).reorder('GREATEST(renewal_date, created_at) DESC')

Upvotes: 3

Related Questions