Rails：最后以空值排序

Question

在我的 Rails 应用程序中，我遇到了几次问题，我想知道其他人如何解决：

我有某些记录，其中值是可选的，因此有些记录具有值，有些记录对该列为空。

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

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

如果我这样做Photo.order('collection_id desc) 然后在 SQLite 上我最后得到空值，但在 PostgreSQL 上我首先得到空值。

有没有一种好的、标准的 Rails 方法来处理这个问题并在任何数据库中获得一致的性能？

Answer 1

尽管现在已经是 2017 年了，但对于 NULL 是否应该优先尚未达成共识。如果您没有明确说明，您的结果将根据 DBMS 的不同而有所不同。

该标准没有指定 NULL 与非 NULL 值相比应如何排序，但任何两个 NULL 都被视为同等排序，并且 NULL 应该排序在所有非 NULL 值之上或之下。< /p>

来源，大多数 DBMS 的比较

为了说明这个问题，我编制了一个列表在 Rails 开发中，有几个最常见的情况：

PostgreSQL

NULL 具有最高的值。

默认情况下，空值排序就好像大于任何非空值。

来源：PostgreSQL 文档

MySQL

NULL s 具有最低值。

执行 ORDER BY 时，如果执行 ORDER BY ... ASC，则首先显示 NULL 值；如果执行 ORDER BY ... DESC，则最后显示 NULL 值。

来源：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。

Answer 2

在column_name前面加上减号并反转顺序方向。它适用于 mysql。 更多详细信息

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

Answer 3

Photo.order('collection_id DESC NULLS LAST')

我知道这是旧的，但我刚刚找到了这个片段，它对我有用。

Answer 4

虽然有点晚了，但有一个通用的 SQL 方法可以做到这一点。像往常一样，CASE 来救援。

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

Answer 5

Rails 6.1 向 Arel for 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

Answer 6

最简单的方法是使用：

.order('name nulls first')

Answer 7

为了后代的利益，我想强调与 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 问题中引用了这一点（一个, 两个，

  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

唯一的缺点是必须针对每个范围完成此链接。

Answer 8

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

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

但不幸的是，一些提出的解决方案，包括“通用解决方案”和“PostgreSQL 和 MySQL 特定的”，将不再适用于 Rails 6 (ActiveRecord 6)，因为这是因为 order() 规范发生变化，不接受某些原始 SQL（我确认“PostgreSQL 特定”解决方案在 Rails 6.1.4 中仍然有效）。有关此更改的背景，请参阅例如
"Rails 6.1 中 SQL 注入的更新”贾斯汀。

为了避免这个问题，您可以使用 来封装 SQL 语句Arel.sql 如下，其中 NULL 位于最后，假设您 100% 确定您给出的 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 没有 Boolean 类型，因此前者可能更安全，尽管这并不重要，因为 Rails 应该保证该值为 0 或 1（或 NULL）。）

Answer 9

就我而言，我需要按 ASC 按开始日期和结束日期对行进行排序，但在少数情况下 end_date 为空，并且该行应该在上面，我使用

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

Answer 10

将数组添加在一起将保留顺序：

@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

Answer 11

如果您希望跨数据库类型获得一致的结果，似乎您必须在 Ruby 中执行此操作，因为数据库本身会解释 NULL 是否位于列表的前端或末尾。

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

但这效率不高。

Answer 12

我不是 SQL 专家，但为什么不先按是否为 null 进行排序，然后再按您想要的方式对其进行排序。

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

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

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

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

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