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?
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')