sql - Time based priority in Active Record Query

Question

I have a table which has job listings, which when displayed are normally ordered by the created_at field descending. I am in the process of adding a "featured" boolean flag which would add the ability for customers to get more visibility to their job listing. I'd like to have the featured listings pinned to the top of the search results if the job is less than X days old. How would I modify by existing query to support this?

Jobs.where("expiration_date >= ? and published = ?", Date.today, true).order("created_at DESC") 

Current query pulls back all current, published jobs, ordered by created_at.

Answer 1

Unlike some other databases (like Oracle) PostgreSQL has a fully functional boolean type. You can use it directly in an ORDER BY clause without applying a CASE statement - those are great for more complex situations.

Sort order for boolean values is:

FALSE -> TRUE -> NULL

If you ORDER BY bool_expressionDESC, you invert the order to:

NULL -> TRUE -> FALSE

If you want TRUE first and NULL last, use the NULLS LAST clause of ORDER BY:

ORDER BY (featured AND created_at > now() - interval '11 days') DESC NULLS LAST  
       , created_at DESC

Of course, NULLS LAST is only relevant if featured or created_at can be NULL. If the columns are defined NOT NULL, then don't bother.

Also, FALSE would be sorted before NULL. If you don't want to distinguish between these two, you are either back to a CASE statement, or you can throw in NULLIF() or COALESCE().

ORDER BY NULLIF(featured AND created_at > now() - interval '11 days'), FALSE)
                                                                DESC NULLS LAST
       , created_at DESC

Performance

Note, how I used:

created_at > now() - interval '11 days'

and not:

now() - created_at < interval '11 days'

In the first example, the expression to the right is a constant that is calculated once. Then an index can be utilized to look up matching rows. Very efficient.

The latter cannot usually be used with an index. A value has to be computed for every single row, before it can be checked against the constant expression to the right. Don't do this if you can avoid it. Ever!