Ruby-on-rails Rails：最后用空值排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5826210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails: Order with nulls last
提问by Andrew
In my Rails app I've run into an issue a couple times that I'd like to know how other people solve:
在我的 Rails 应用程序中，我遇到了几次我想知道其他人如何解决的问题：
I have certain records where a value is optional, so some records have a value and some are null for that column.
我有某些记录的值是可选的，因此某些记录具有该列的值，而某些记录为空。
If I order by that column on some databases the nulls sort first and on some databases the nulls sort last.
如果我在某些数据库上按该列排序，则空值首先排序，而在某些数据库中空值排序最后。
For instance, I have Photos which may or may not belong to a Collection, ie there are some Photos where collection_id=niland some where collection_id=1etc.
例如，我的照片可能属于也可能不属于一个集合，即有一些照片在哪里collection_id=nil和一些在哪里collection_id=1等。
If I do Photo.order('collection_id desc)then on SQLite I get the nulls last but on PostgreSQL I get the nulls first.
如果我Photo.order('collection_id desc)在 SQLite 上这样做，我最后得到空值，但在 PostgreSQL 上我首先得到空值。
Is there a nice, standard Rails way to handle this and get consistent performance across any database?
有没有一种很好的标准 Rails 方法来处理这个问题并在任何数据库中获得一致的性能？
采纳答案by Eric
Adding arrays together will preserve order:
将数组添加在一起将保持顺序：
@nonull = Photo.where("collection_id is not null").order("collection_id desc")
@yesnull = Photo.where("collection_id is null")
@wanted = @nonull+@yesnull
回答by Intentss
I'm no expert at SQL, but why not just sort by if something is null first then sort by how you wanted to sort it.
我不是 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
If you are only using PostgreSQL, you can also do this
如果你只使用 PostgreSQL，你也可以这样做
Photo.order('collection_id DESC NULLS LAST') #Null's Last
Photo.order('collection_id DESC NULLS FIRST') #Null's First
If you want something universal (like you're using the same query across several databases, you can use (courtesy of @philT)
如果你想要一些通用的东西（比如你在多个数据库中使用相同的查询，你可以使用（由@philT 提供）
Photo.order('CASE WHEN collection_id IS NULL THEN 1 ELSE 0 END, collection_id')
回答by Adam Sibik
Even though it's 2017 now, there is still yet to be a consensus on whether NULLs should take precedence. Without you being explicit about it, your results are going to vary depending on the DBMS.
尽管现在是 2017 年，但对于NULLs是否应该优先，仍有待达成共识。如果您没有明确说明，您的结果将因 DBMS 而异。
The standard doesn't specify how NULLs should be ordered in comparison with non-NULL values, except that any two NULLs are to be considered equally ordered, and that NULLs should sort either above or below all non-NULL values.
该标准没有指定 NULL 与非 NULL 值相比应该如何排序，除了任何两个 NULL 都被认为是同等排序的，并且 NULL 应该排序在所有非 NULL 值的上方或下方。
To illustrate the problem, I compiled a list of a few most popular cases when it comes to Rails development:
为了说明这个问题，我整理了一些 Rails 开发中最流行的案例：
PostgreSQL
PostgreSQL
NULLs have the highest value.
NULLs 的值最高。
By default, null values sort as if larger than any non-null value.
默认情况下，空值的排序就像大于任何非空值一样。
MySQL
MySQL
NULLs have the lowest value.
NULLs 的值最低。
When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.
执行 ORDER BY 时，如果执行 ORDER BY ... ASC，则首先显示 NULL 值，如果执行 ORDER BY ... DESC，则最后显示。
SQLite
SQLite
NULLs have the lowest value.
NULLs 的值最低。
A row with a NULL value is higher than rows with regular values in ascending order, and it is reversed for descending order.
NULL 值的行按升序比普通值的行高，降序相反。
Solution
解决方案
Unfortunately, Rails itself doesn't provide a solution for it yet.
不幸的是，Rails 本身还没有提供解决方案。
PostgreSQL specific
特定于 PostgreSQL
For PostgreSQL you could quite intuitively use:
对于 PostgreSQL，您可以非常直观地使用：
Photo.order('collection_id DESC NULLS LAST') # NULLs come last
Photo.order('collection_id DESC NULLS LAST') # NULLs come last
MySQL specific
特定于 MySQL
For MySQL, you could put the minus sign upfront, yet this feature seems to be undocumented. Appears to work not only with numerical values, but with dates as well.
对于 MySQL，您可以将减号放在前面，但此功能似乎没有记录。似乎不仅适用于数值，还适用于日期。
Photo.order('-collection_id DESC') # NULLs come last
Photo.order('-collection_id DESC') # NULLs come last
PostgreSQL and MySQL specific
PostgreSQL 和 MySQL 特定的
To cover both of them, this appears to work:
为了涵盖它们，这似乎有效：
Photo.order('collection_id IS NULL, collection_id DESC') # NULLs come last
Photo.order('collection_id IS NULL, collection_id DESC') # NULLs come last
Still, this one does not workin SQLite.
不过，这个在 SQLite 中不起作用。
Universal solution
通用解决方案
To provide cross-support for all DBMSs you'd have to write a query using CASE, already suggested by @PhilIT:
要为所有 DBMS 提供交叉支持，您必须使用CASE@PhilIT 已经建议的编写查询：
Photo.order('CASE WHEN collection_id IS NULL THEN 1 ELSE 0 END, collection_id')
Photo.order('CASE WHEN collection_id IS NULL THEN 1 ELSE 0 END, collection_id')
which translates to first sorting each of the records first by CASEresults (by default ascending order, which means NULLvalues will be the last ones), second by calculation_id.
这意味着首先按CASE结果对每个记录进行排序（默认情况下是升序，这意味着NULL值将是最后一个），然后按calculation_id.
回答by Thomas Yancey
Photo.order('collection_id DESC NULLS LAST')
I know this is an old one but I just found this snippet and it works for me.
我知道这是一个旧的，但我刚刚找到了这个片段，它对我有用。
回答by raymondralibi
Put minus signin front of column_name and reverse the order direction. It works on mysql. More details
放减号在列名的前面和反向顺序的方向。它适用于mysql。更多细节
Product.order('something_date ASC') # NULLS came first
Product.order('-something_date DESC') # NULLS came last
回答by PhilT
Bit late to the show but there is a generic SQL way to do it. As usual, CASEto the rescue.
节目有点晚了，但有一种通用的 SQL 方法可以做到这一点。像往常一样，CASE救人。
Photo.order('CASE WHEN collection_id IS NULL THEN 1 ELSE 0 END, collection_id')
回答by Lanny Bose
For posterity's sake, I wanted to highlight an ActiveRecord error relating to NULLS FIRST.
为了后代的缘故，我想强调一个与NULLS FIRST.
If you try to call:
如果您尝试调用：
Model.scope_with_nulls_first.last
Rails will attempt to call reverse_order.first, and reverse_orderis not compatible with NULLS LAST, as it tries to generate the invalid SQL:
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...
This was referenced a few years ago in some still-open Rails issues (one, two, three). I was able to work around it by doing the following:
几年前在一些尚未解决的 Rails 问题（一、二、三）中引用了这一点。我能够通过执行以下操作来解决它：
scope :nulls_first, -> { order("table_column IS NOT NULL") }
scope :meaningfully_ordered, -> { nulls_first.order("table_column ASC") }
It appears that by chaining the two orders together, valid SQL gets generated:
似乎通过将两个订单链接在一起，生成了有效的 SQL：
Model Load (12.0ms) SELECT "models".* FROM "models" ORDER BY table_column IS NULL DESC, table_column ASC LIMIT 1
The only downside is that this chaining has to be done for eachscope.
唯一的缺点是必须对每个作用域进行这种链接。
回答by Jean-Etienne Durand
The easiest way is to use:
最简单的方法是使用：
.order('name nulls first')
.order('name nulls first')
回答by Dmitriy Gusev
In my case I needed sort lines by start and end date by ASC, but in few cases end_date was null and that lines should be in above, I used
在我的情况下，我需要按开始和结束日期按 ASC 对行进行排序，但在少数情况下 end_date 为空并且这些行应该在上面，我使用
@invoice.invoice_lines.order('start_date ASC, end_date ASC NULLS FIRST')
@invoice.invoice_lines.order('start_date ASC, end_date ASC NULLS FIRST')
回答by cseelus
First get Photos where collection_idis not null and order by collection_iddescending.
首先获取collection_id不为空的照片并按collection_id降序排列。
Then get Photos where collection_idis null and add them.
然后获取collection_id为空的照片并添加它们。
Photos.where.not(collection_id: [nil, ""])
.order(collection_id: :desc) + where(collection_id: [nil, ""])
