13

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?

mu is too short
  • 426,620
  • 70
  • 833
  • 800
Andrew
  • 42,517
  • 51
  • 181
  • 281

5 Answers5

17

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • @Andrew: I should have waited a couple months for the one year anniversary :) – mu is too short Jan 21 '12 at 17:43
  • 1
    There seems to be a better way: order('average_rating IS NULL, average_rating DESC'), have a look at http://stackoverflow.com/a/7055259/496209 – Luksurious Oct 08 '13 at 02:12
  • @Luksurious: The problem with that is that there is no documented and portable ordering for booleans. So, to get reliable results you're back to a `case when average_rating is null then ...`. – mu is too short Oct 08 '13 at 03:43
  • @Luksurious: MySQL uses 1 and 0 for booleans so booleans sort like any other number. PostgreSQL has native booleans and I've never been able to find a defined sort order for them so they work but not "officially". SQLite uses 1 and 0 for booleans but Rails (incorrectly) uses `'t'` and `'f'` strings for booleans so boolean sort is either numeric or string sorting depending on context. Other databases will do other things. Boolean sorting is a bit of a mess so I just use a CASE and do it by hand. – mu is too short Oct 08 '13 at 22:38
11

Try this :)

scope :best, order("average_rating DESC NULLS LAST")
  • 1
    Out of curiosity, because I'm a total SQL noob, will that work across all SQL drivers? I need it to work at least on SQLite and PostgreSQL. – Andrew Apr 02 '11 at 13:14
  • Ok, this doesn't work on SQLite, so I can't use it. Still searching. – Andrew Apr 02 '11 at 15:40
  • 1
    This is a nice solution, but seems quite buggy. It sometimes causes a SQL error, which I think is what's raised here: https://github.com/rails/rails/issues/11571 Unfortunately the Rails team just closed the issue without fixing it :\ – Arepo Aug 02 '17 at 18:04
2

while the coalesce SQL method is great, if your database does not support this, here is a method that should be supported along all databaseses:

order("-average_rating DESC")

This will order your records and put the NULL to the end.

Community
  • 1
  • 1
Andre Schweighofer
  • 2,759
  • 1
  • 26
  • 25
  • This is the only answer that worked as expected for me. Doing something like `order "-due_date desc, due_date"` to sort by date, with nils at the end. – DannyB Aug 19 '15 at 21:21
2

I know this was awhile ago but wouldn't this work across a few

order("ISNULL(average_rating) DESC")
bokor
  • 1,829
  • 1
  • 19
  • 25
  • I think SQL Server and MySQL have that (not sure if they work the same in both though), PostgreSQL doesn't, and neither does SQLite. – mu is too short Jan 21 '12 at 00:50
  • yes I believe you are correct...would be nice to see the PostgreSQL equivalent if there was one..haven't really used that DB much yet! – bokor Apr 10 '12 at 04:03
  • someone put this on the postgreSQL question about ISNULL - http://stackoverflow.com/questions/2214525/what-is-the-postgresql-equivalent-for-isnull – bokor Apr 10 '12 at 04:05
0

Well, I never found an approach that would work across DB drivers, so I just forced the value of all the records that were previously nil to be zero. That solved it for me, although it was a bit brutish. When the timer runs out I'll accept this since it is the solution I used, but if anyone revisits this and wants to provide an alternative answer in the future I'll be happy to accept a different answer later on.

Andrew
  • 42,517
  • 51
  • 181
  • 281