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?

user160917 · Answer 1 · 2023-07-24T16:27:00.737

306

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

edited Jul 24 '23 at 16:27

answered Aug 14 '11 at 05:42

user160917

9,211
4
53
63

22

+1, much better than the accepted answer and can be expressed via Arel – m_x Dec 18 '12 at 14:57
I agree this is a better answer, but don't see this as supported in arel out of the box. @m_x, are you referring to https://gist.github.com/jswanner/3717188, or am I missing something? – eprothro May 07 '14 at 16:48
1

wow, this is an old comment ! So I guess what I was trying to say was : `p = Photo.arel_table; Photo.order(p[:collection_id].eq(nil)).order(p[:collection_id].desc)` – m_x May 09 '14 at 07:48
This is the correct answer and is cross-database compatible. Arel has nothing to do with the question. It's like saying, "But this doesn't work on my programmable calculator!" Who cares, that wasn't the issue. – Dec 01 '14 at 00:18
Using NULLS FIRST on a Rails 3 app with PostGres fails if you add another sort order, even using something like Post.order("collection_id DESC NULLS LAST").first. The second method works, however. – you786 Mar 19 '15 at 17:13
3

Note that `NULLS LAST` does not allow you to chain `.last()` onto your query as of Rails 4.2. I posted a work-around below. – Lanny Bose Oct 09 '15 at 21:51
Gosh, what a swell answer! – fatfrog Jul 07 '16 at 14:46
1

Why does ordering by `IS NULL` put the NULL rows last? You would think it would put them first. – jackocnr Jul 14 '17 at 17:06
2

@jackocr myguess: IS NULL evaluates to 1 if true, 0 if false, sorted, 0 comes before 1 ... – user160917 Jul 27 '17 at 17:35
@MikeBethany As stated in the answer itself, it **is not** compatible across different database systems. – Adam Sibik Jul 16 '18 at 13:53
Any idea how to do this without string syntax? – BenKoshy Sep 05 '22 at 20:42

score 44 · Answer 2 · answered Jul 04 '17 at 19:27

Even though it's 2017 now, there is still yet to be a consensus on whether NULLs 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:

PostgreSQL

NULLs have the highest value.

By default, null values sort as if larger than any non-null value.

source: PostgreSQL documentation

MySQL

NULLs 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

SQLite

NULLs 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

Solution

Unfortunately, Rails itself doesn't provide a solution for it yet.

PostgreSQL specific

For PostgreSQL you could quite intuitively use:

Photo.order('collection_id DESC NULLS LAST') # NULLs come last

MySQL specific

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

PostgreSQL and MySQL specific

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.

Universal solution

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.

It appears that ActiveRecord 6 no longer permits raw sql, as in the "Universal solution" above. I'd love to know how this could be done in Rails 6. — Les Nightingill, Jun 16 '21 at 15:41
@LesNightingill That is right. In short, use `Arel.sql` to circumvent the problem. I have just added my answer for Rails 6. — Masa Sakano, Aug 08 '21 at 07:14

score 15 · Answer 3 · edited Jul 26 '16 at 20:47

15

Photo.order('collection_id DESC NULLS LAST')

I know this is an old one but I just found this snippet and it works for me.

edited Jul 26 '16 at 20:47

Paul Roub

36,322
27
84
93

answered Jul 26 '16 at 20:32

Thomas Yancey

391
4
7

I dont know in what version of Ruby/Rails this worked, but for ruby 2.5 and rails 5 doesnt seem to work. – Tasos Anesiadis Jan 27 '20 at 07:15
This is PostgreSQL specific – Suleman Uzair Jun 02 '23 at 11:29

score 14 · Answer 4 · answered Jan 30 '13 at 02:05

14

Put minus sign in front of column_name and reverse the order direction. It works on mysql. More details

Product.order('something_date ASC') # NULLS came first
Product.order('-something_date DESC') # NULLS came last

answered Jan 30 '13 at 02:05

raymondralibi

1,933
1
17
25

score 11 · Answer 5 · answered Aug 28 '15 at 07:12

11

Bit late to the show but there is a generic SQL way to do it. As usual, CASE to the rescue.

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

answered Aug 28 '15 at 07:12

PhilT

4,166
1
36
26

score 9 · Answer 6 · answered Sep 09 '21 at 09:22

9

Rails 6.1 adds nulls_first and nulls_last methods to Arel for PostgreSQL.

Example:

User.order(User.arel_table[:login_count].desc.nulls_last)

Source: https://www.bigbinary.com/blog/rails-6-1-adds-nulls-first-and-nulls-last-to-arel

answered Sep 09 '21 at 09:22

David Morales

17,816
12
77
105

score 7 · Answer 7 · answered Feb 23 '12 at 19:59

7

The easiest way is to use:

.order('name nulls first')

answered Feb 23 '12 at 19:59

Jean-Etienne Durand

79
1
1

3

This is best for Postgres – Sergii Mostovyi Dec 15 '14 at 12:21
1

This is good solution, but beware that ActiveRecord `last` seems to insert an errant DESC. – sirvine May 26 '15 at 13:15

score 6 · Answer 8 · answered Oct 09 '15 at 21:50

For posterity's sake, I wanted to highlight an ActiveRecord error relating to NULLS FIRST.

If you try to call:

Model.scope_with_nulls_first.last

Rails will attempt to call reverse_order.first, and reverse_order is not compatible with NULLS LAST, as it tries to generate the invalid SQL:

PG::SyntaxError: ERROR:  syntax error at or near "DESC"
LINE 1: ...dents"  ORDER BY table_column DESC NULLS LAST DESC LIMIT...

This was referenced a few years ago in some still-open Rails issues (one, two, three). I was able to work around it by doing the following:

  scope :nulls_first, -> { order("table_column IS NOT NULL") }
  scope :meaningfully_ordered, -> { nulls_first.order("table_column ASC") }

It appears that by chaining the two orders together, valid SQL gets generated:

Model Load (12.0ms)  SELECT  "models".* FROM "models"  ORDER BY table_column IS NULL DESC, table_column ASC LIMIT 1

The only downside is that this chaining has to be done for each scope.

Masa Sakano · Answer 9 · 2021-08-08T07:25:46.707

Here are some Rails 6 solutions.

The answer by @Adam Sibik is a great summary about the difference between various database systems.

Unfortunately, though, some of the presented solutions, including "Universal solution" and "PostgreSQL and MySQL specific", would not work any more with Rails 6 (ActiveRecord 6) as a result of its changed specification of order() not accepting some raw SQLs (I confirm the "PostgreSQL specific" solution still works as of Rails 6.1.4). For the background of this change, see, for example,
"Updates for SQL Injection in Rails 6.1" by Justin.

To circumvent the problem, you can wrap around the SQL statements with Arel.sql as follows, where NULLs come last, providing you are 100% sure the SQL statements you give are safe.

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

Just for reference, if you want to sort by a Boolean column (is_ok, as an example) in the order of [TRUE, FALSE, NULL] regardless of the database systems, either of these should work:

Photo.order(Arel.sql('CASE WHEN is_ok IS NULL THEN 1 ELSE 0 END, is_ok DESC'))
Photo.order(Arel.sql('CASE WHEN is_ok IS NULL THEN 1 WHEN is_ok IS TRUE THEN -1 ELSE 0 END'))

(n.b., SQLite does not have the Boolean type and so the former may be safer arguably, though it should not matter because Rails should guarantee the value is either 0 or 1 (or NULL).)

score 1 · Answer 10 · answered Feb 02 '17 at 09:26

1

In my case I needed sort lines by start and end date by ASC, but in few cases end_date was null and that lines should be in above, I used

@invoice.invoice_lines.order('start_date ASC, end_date ASC NULLS FIRST')

answered Feb 02 '17 at 09:26

Dmitriy Gusev

151
1
6

score -4 · Answer 11 · answered Apr 29 '11 at 00:18

It seems like you'd have to do it in Ruby if you want consistent results across database types, as the database itself interprets whether or not the NULLS go at the front or end of the list.

Photo.all.sort {|a, b| a.collection_id.to_i <=> b.collection_id.to_i}

But that is not very efficient.

score -4 · Accepted Answer · answered Apr 29 '11 at 06:11

-4

Adding arrays together will preserve order:

@nonull = Photo.where("collection_id is not null").order("collection_id desc")
@yesnull = Photo.where("collection_id is null")
@wanted = @nonull+@yesnull

http://www.ruby-doc.org/core/classes/Array.html#M000271

answered Apr 29 '11 at 06:11

Eric

2,539
18
23

2

Well, I don't love this idea, but I think this would work. Sorry to have left it open so long, I was hoping that some other answers would appear. Having spent some more time thinking about it though, I think this could be made into a method on the Photo model and then it wouldn't feel too bad. – Andrew May 13 '11 at 01:52
It's easy if you're using mysql. See my solution. – Jacob Dec 10 '11 at 00:11
Right, I should have mentioned that mine was only the most agnostic way I could find. – Eric Dec 11 '11 at 00:26
11

This is a bad idea since `where` doesn't return an array, it returns an ActiveRecord::Relation and forcing the results into an array will cause everything that expects a standard ActiveRecord::Relation to fail (like pagination). – Jul 27 '14 at 15:37
1

True, though it appears the available methods either break out of AR or aren't (completely) portable. – Eric Jul 28 '14 at 21:44