Rails: Order with nulls last

Question

In my Rails app I've run into an issue a couple times that I'd like to know how other people solve:

I have certain records where a value is optional, so some records have a value and some are null for that column.

If I order by that column on some databases the nulls sort first and on some databases the nulls sort last.

For instance, I have Photos which may or may not belong to a Collection, ie there are some Photos where collection_id=nil and some where collection_id=1 etc.

If I do Photo.order('collection_id desc) then on SQLite I get the nulls last but on PostgreSQL I get the nulls first.

Is there a nice, standard Rails way to handle this and get consistent performance across any database?

Answer 1

I'm no expert at SQL, but why not just sort by if something is null first then sort by how you wanted to sort it.

Photo.order('collection_id IS NULL, collection_id DESC')  # Null's last
Photo.order('collection_id IS NOT NULL, collection_id DESC') # Null's first

If you are only using PostgreSQL, you can also do this

Photo.order('collection_id DESC NULLS LAST')  #Null's Last
Photo.order('collection_id DESC NULLS FIRST') #Null's First

If you want something universal (like you're using the same query across several databases, you can use (courtesy of @philT)

Photo.order('CASE WHEN collection_id IS NULL THEN 1 ELSE 0 END, collection_id')