2

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.

cman77
  • 1,753
  • 1
  • 22
  • 48

2 Answers2

3

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!

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    This is a better approach, I can always count on you to correct my (Postgre)?SQL brain damage :) You shouldn't have to worry about NULLs in `created_at` in a Rails app, that should never (ha ha) happen. – mu is too short Oct 01 '12 at 21:53
  • my mind is officially blown - this did it `select id, featured, created_at, title from jobs where published = true and expiration_date >= '10/1/2012' order by (featured and created_at > now() - interval '2 days') desc nulls last, created_at desc;` – cman77 Oct 02 '12 at 00:25
0

Not sure what you want to achieve here.

I guess you'll be paginating the results. If so, and you want to display featured jobs always on top, regardless of the page, then you should pull them from the DB separately. If you just want to display them on the first page, order by published like this :

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

If you want to pull them separately :

@featured_jobs = Jobs.where("expiration_date >= ? and published = ?", Date.today, true).order("created_at DESC")
@regular_jobs = Jobs.where("expiration_date >= ? AND published = ?", Date.today, false).order("created_at DESC") #Paginate them too ... depends on the gem you're using
Anthony Alberto
  • 10,325
  • 3
  • 34
  • 38
  • Thanks Anthony. My goal is to have the featured jobs pinned @ the top only if they are less than 7 days old. After that they fall into the regular created_at ordering. Was hoping to do this in one query rather than two. If no one provides a solution in a single query, I'll mark this as the accepted answer – cman77 Oct 01 '12 at 19:46
  • Can't think of a single SQL request that could handle this ... anyway if you add pagination, you'll need to get the featured jobs separately I think! – Anthony Alberto Oct 01 '12 at 20:46