Rails 6.1 adds nulls_first and nulls_last methods to Arel for PostgreSQL

In PostgreSQL, when sorting output rows in descending order, columns with null values will appear first.

Let's take this example of ordering users by the number of times they have logged in.

1
2postgres=> SELECT * from users ORDER BY login_count DESC;
3
4       name        | login_count
5-------------------+-------------
6 Johnny Silverhand |        NULL
7 Jackie Welles     |         202
8 V                 |           1
9
10(3 rows)
11
Copy

This is not useful since most of the time we would want the null values to appear last.

PostgreSQL provides NULLS FIRST and NULLS LAST options for the ORDER BY clause for this use case.

1
2irb> pp User.order("login_count DESC NULLS LAST").
3        pluck(:name, :login_count)
4
5(0.9 ms)  SELECT "users"."name", "users"."login_count" FROM "users"
6          ORDER BY login_count DESC NULLS LAST
7
8=> [["Jackie Welles", 202],
9   ["V", 1],
10   ["Johnny Silverhand", nil]]
11
Copy

In Rails 6.1, we can use the new nulls_first or nulls_last methods to construct the same query using Arel.

1
2irb> pp User.order(User.arel_table[:login_count].desc.nulls_last).
3        pluck(:name, :login_count)
4
5(0.9 ms)  SELECT "users"."name", "users"."login_count" FROM "users"
6          ORDER BY login_count DESC NULLS LAST
7
8=> [["Jackie Welles", 202],
9   ["V", 1],
10   ["Johnny Silverhand", nil]]
11
Copy

The resulting code is slightly more verbose for this simple example. But Arel really shines when programmatically constructing complex SQL queries.

Check out this pull request for more details.