Rails: 将空值排在最后的排序

Question

在我的Rails app中，我遇到了一些问题，我想知道其他人是如何解决的：

有些记录的某个值是可选的，因此某些记录在该列中的值为空(null)，而有些记录则不为空。

如果在某些数据库上按该列排序，则null在某些数据库上首先排序，而在某些数据库上最后排序。

例如，我有一些照片可能属于集合，即有些照片的collection_id=nil，有些照片的collection_id=1等。

如果我执行Photo.order('collection_id desc')，那么在SQLite上我会得到nulls在最后，但在PostgreSQL上我会得到nulls在最前面。

是否有一种好的、标准的Rails方法来处理这个问题并获得在任何数据库中都能保持性能一致的结果呢？

- Andrew

Answer 1

我对SQL并不是很精通，但为什么不先按照是否为空进行排序，然后再按照你想要的方式进行排序呢？

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

如果你只使用PostgreSQL，你也可以这样做。

Photo.order('collection_id DESC NULLS LAST')  #Null's Last
Photo.order('collection_id DESC NULLS FIRST') #Null's First

如果你想要一个通用的东西（比如在多个数据库中使用相同的查询），你可以使用（由@philT提供的链接）。

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

- user160917

---

好的，我会尽力为您翻译这段内容。请看以下翻译：+1，比被接受的答案要好得多，并且可以通过 Arel 表达。 - m_x

哇，这是一个旧评论！所以我想说的是：p = Photo.arel_table; Photo.order(p[:collection_id].eq(nil)).order(p[:collection_id].desc) - m_x

请注意，自Rails 4.2起，NULLS LAST不能让您将.last()链接到查询中。 我在下面发布了一个解决方法。 - Lanny Bose

为什么按IS NULL排序会把NULL行放在最后？你可能认为它会把它们放在最前面。 - jackocnr

@jackocr 我的猜测是：如果为真，则IS NULL计算结果为1，如果为假，则计算结果为0，排序时，0排在1之前... - user160917

Answer 2

虽然现在已经是2017年了，但是关于NULL应该如何优先处理仍然没有共识。如果您不明确说明，那么您的结果将因DBMS而异。

标准并未指定如何比较NULL和非NULL值的顺序，除了任何两个NULL都被认为是等有序的，并且NULL应该在所有非NULL值之上或之下排序。

来源，大多数DBMS的比较

为了说明问题，我列出了一些最流行的Rails开发案例：

PostgreSQL

NULL具有最高的值。

默认情况下，null值排序时会被认为比任何非null值都要大。

来源：PostgreSQL文档

MySQL

NULL具有最低的值。

在进行ORDER BY时，如果使用ORDER BY ... ASC，则NULL值将首先呈现；如果使用ORDER BY ... DESC，则它们将最后呈现。 来源:MySQL文档 SQLite NULL具有最低的值。

当按升序排列时，具有NULL值的行高于具有常规值的行，并且在按降序排序时相反。 来源 解决方案

不幸的是，Rails本身还没有提供解决方案。

PostgreSQL特定

对于PostgreSQL，您可以直观地使用： Photo.order('collection_id DESC NULLS LAST')# NULLs come last MySQL特定

对于MySQL，您可以将减号放在前面，但似乎此功能未经记录。 它不仅适用于数字值，还适用于日期。

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

PostgreSQL和MySQL特定内容

为了涵盖两种数据库，这个看起来是可以的：

Photo.order('collection_id IS NULL, collection_id DESC') # NULLs come last

但是，在SQLite中，这个无法工作。

通用解决方案

为了提供所有DBMS的交叉支持，您需要编写一个使用CASE的查询，@PhilIT已经建议过了：

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

它首先按照每条记录的CASE结果排序（默认升序，这意味着NULL值将是最后一个），然后再按照calculation_id排序。

- Adam Sibik

---

似乎ActiveRecord 6不再允许使用原始SQL，例如上面的“通用解决方案”。我想知道在Rails 6中该如何实现。 - Les Nightingill

@LesNightingill 没错。简而言之，使用 Arel.sql 来规避这个问题。我刚刚为 Rails 6 添加了我的答案。 - Masa Sakano

Answer 3

Photo.order('collection_id DESC NULLS LAST')

我知道这是一个老的例子，但我刚找到了这个代码片段，它对我有用。

- Thomas Yancey

---

我不知道这个在哪个版本的Ruby/Rails中可以工作，但是对于Ruby 2.5和Rails 5似乎不起作用。 - Tasos Anesiadis

这是针对PostgreSQL特定的。 - Suleman Uzair

Answer 4

在列名前加上减号，可以反转排序顺序。该方法适用于MySQL数据库。更多详情

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

- raymondralibi

Answer 5

可能有些晚了，但是有一种通用的 SQL 方法可以做到。像往常一样，CASE 可以解决问题。

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

- PhilT

Answer 6

Rails 6.1为PostgreSQL添加了nulls_first和nulls_last方法。

示例：

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

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

Rails 6.1 增加了 nulls_first 和 nulls_last 方法到 Arel。使用这些方法可以让我们在 SQL 中更好地处理 NULL 值的排序。在排序时，nulls_first 将 NULL 值定义为最小值，而 nulls_last 则将其定义为最大值。这对于需要按特定顺序排列包含 NULL 值的结果集的查询非常有用。

- David Morales

Answer 7

最简单的方法是使用：

.order('name nulls first')

这将按名称排序，并将空值放在第一位。

- Jean-Etienne Durand

---

这对于Postgres来说是最好的。 - Sergii Mostovyi

这是一个好的解决方案，但要注意ActiveRecord的last似乎会插入一个错误的DESC。 - sirvine

Answer 8

为了后人的纪念，我想要强调与NULLS FIRST有关的ActiveRecord错误。

如果你尝试调用：

Model.scope_with_nulls_first.last

Rails将尝试调用reverse_order.first，而reverse_order与NULLS LAST不兼容，因为它试图生成无效的SQL：

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

几年前，在一些仍然未解决的Rails问题中提到过这个问题（one，two，three）。我能够通过以下方式解决它：

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

看起来通过将这两个命令链接在一起，就能生成有效的SQL语句:

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

唯一的缺点是这种链接必须为每个作用域执行。

- Lanny Bose

Answer 9

以下是一些Rails 6的解决方案。

@Adam Sibik的答案很好地概述了各种数据库系统之间的差异。

然而，遗憾的是，其中一些提出的解决方案，包括“通用解决方案”和“针对PostgreSQL和MySQL的解决方案”，由于其改变了order()规范不再接受一些原始SQL，因此在Rails 6（ActiveRecord 6）中不再起效（我确认，“针对PostgreSQL的特定解决方案”仍适用于Rails 6.1.4）。有关这种变化的背景，例如，请参见Justin撰写的"Rails 6.1中SQL注入的更新"。

为了解决此问题，您可以使用Arel.sql包装SQL语句，其中包含NULL值，前提是您确信所提供的SQL语句是安全的。

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

仅供参考，如果您想按布尔列（例如is_ok）以[TRUE，FALSE，NULL]的顺序进行排序，而不考虑数据库系统，则以下任何一个都应该可以：

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

（注意，SQLite没有布尔类型，所以前者可能更安全，尽管这不应该成问题，因为Rails应该保证值是0或1（或NULL）。）

- Masa Sakano

Answer 10

在我的情况下，我需要按照开始和结束日期按照升序排序行，但是在少数情况下，end_date为空，这些行应该在上面，我使用了 @invoice.invoice_lines.order('start_date ASC, end_date ASC NULLS FIRST')。

- Dmitriy Gusev