BigBinary Blog

We write about Ruby on Rails, React.js, React Native, remote work, open source, engineering and design.

Rails 6.1 adds nulls_first and nulls_last methods to Arel for PostgreSQL

This blog is part of our Rails 6.1 series.

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

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

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

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.

Berin Larson in Rails, Rails 6.1
March 9, 2021
Share

Subscribe to our newsletter