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')
Even though it's 2017 now, there is still yet to be a consensus on whether NULL
s should take precedence. Without you being explicit about it, your results are going to vary depending on the DBMS.
The standard doesn't specify how NULLs should be ordered in comparison with non-NULL values, except that any two NULLs are to be considered equally ordered, and that NULLs should sort either above or below all non-NULL values.
source, comparison of most DBMSs
To illustrate the problem, I compiled a list of a few most popular cases when it comes to Rails development:
NULL
s have the highest value.
By default, null values sort as if larger than any non-null value.
source: PostgreSQL documentation
NULL
s have the lowest value.
When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.
source: MySQL documentation
NULL
s have the lowest value.
A row with a NULL value is higher than rows with regular values in ascending order, and it is reversed for descending order.
source
Unfortunately, Rails itself doesn't provide a solution for it yet.
For PostgreSQL you could quite intuitively use:
Photo.order('collection_id DESC NULLS LAST') # NULLs come last
For MySQL, you could put the minus sign upfront, yet this feature seems to be undocumented. Appears to work not only with numerical values, but with dates as well.
Photo.order('-collection_id DESC') # NULLs come last
To cover both of them, this appears to work:
Photo.order('collection_id IS NULL, collection_id DESC') # NULLs come last
Still, this one does not work in SQLite.
To provide cross-support for all DBMSs you'd have to write a query using CASE
, already suggested by @PhilIT:
Photo.order('CASE WHEN collection_id IS NULL THEN 1 ELSE 0 END, collection_id')
which translates to first sorting each of the records first by CASE
results (by default ascending order, which means NULL
values will be the last ones), second by calculation_id
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With