4

Rails 7 supports NullsFirst for all databases

 2 years ago
source link: https://blog.saeloun.com/2021/10/12/support-nulls_first-for-all-databases
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

In the beginning of this year, Rails 6.1 added support for nulls_first and nulls_last Arel methods for PostgreSQL. This was a great addition since, unlike most databases, PostgreSQL would push data with null values to the top when ordering columns. However, in most situations, this was not preferred.

Before

Rails 6.1 introduced the following Arel methods only to PostgreSQL databases to control null data ordering.

irb> User.arel_table[:name].desc.nulls_last

(0.9 ms)  SELECT "users"."name" FROM "users"
          ORDER BY name DESC NULLS LAST

=> [["Adam Driver"], ["Bob Dylan"], [""]]

This was fantastic for PostgreSQL users as the default is set to “NULLS FIRST”. Unfortunately, all other databases were missing out on this.

After

Fortunately, ANSI SQL has an option to allow the database to specify where NULLS come out in the sort order,

ORDER BY column ASC NULLS FIRST

MS SQL, SQLite, Oracle, and PostgreSQL all follow this syntax. Unfortunately, MySQL does not.

Rails 7 now introduces support for nulls_first() and nulls_last() for all major databases, except MySQL. When trying to use nulls_last() on MySQL databases, it will throw a runtime error.


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK