Rails: Sort nils to the end of a scope?

Question

So, I have the following scope in my Photo model:

scope :best, order(:average_rating.desc)

The only problem is, the ratings were added to the model after the fact, so the production app has a lot of records where average_rating is nil. When I call this scope it returns all the nils first -- in fact it should be the opposite, nils should be last ( they are photos which have not yet been rated ).

How can I sort nils to the end of this scope?

Answer 1

I'm a bit late to the game but this just came up again and the solution really isn't that difficult.

A portable solution is to use a CASE to turn NULLs into something that will go to the end. If your ratings are non-negative, then -1 is a good choice:

order('case when average_rating is null then -1 else average_rating end desc')

Using COALESCE instead will work in many databases and is a lot less noisy than a CASE:

order('coalesce(average_rating, -1) desc')

If you wanted an ASC sort then the above approaches would put NULLs at the beginning. If you wanted them at the end then you'd use something bigger than your highest rating instead of -1. For example, if you rated things from one to five, you could use 11 to replace NULLs:

order('case when average_rating is null then 11 else average_rating end asc')
order('coalesce(average_rating, 11) asc')

Most people would use 10 but sometimes you need to get a little bit louder so ours go to 11.

You can't really depend on the database putting NULLs at the beginning or end so it is best to be explicit even if you're just reminding your future-self that you've handled the NULL case already.