在我的 Rails 应用程序（使用 postgresql）中，我尝试编写一个 Active Record 查询来查找一组志愿者记录，然后按以下顺序对它们进行排序first_name, then last_name, then email。此外，first_name and last_name may be null（两者都将是null或者两者都不会null）。例如，我希望对以下列表进行排序：
- 志愿者 [名字：'Alex'，姓氏：'Diego'，电子邮件：'[电子邮件受保护] /cdn-cgi/l/email-protection']
- 志愿者 [名字：
null， 姓：null， 电子邮件： '[电子邮件受保护] /cdn-cgi/l/email-protection']
- 志愿者 [名字：'Josh'，姓氏：'Broger'，电子邮件：'[电子邮件受保护] /cdn-cgi/l/email-protection']
- 志愿者 [名字：'Josh'，姓氏：'Broger'，电子邮件：'[电子邮件受保护] /cdn-cgi/l/email-protection']
- 志愿者 [名字：'Josh'，姓氏：'Kenton'，电子邮件：'[电子邮件受保护] /cdn-cgi/l/email-protection']
最初，我有以下代码：
Volunteer.joins(:volunteer_lists).
where("(volunteer_lists.organizer_id = ? AND organizer_type = 'Organization') OR
(volunteer_lists.organizer_id IN (?) AND organizer_type = 'Collaborative')",
self.organization.id, collaboratives).uniq.
order(:first_name, :last_name, :email)
该代码有效，但结果是由志愿者分组的first_name & last_name首先，其他志愿者只email最后（所以在上面的示例列表中，志愿者 #2 将是最后一个）。答案是这篇有用的帖子 https://stackoverflow.com/questions/8120953/order-by-with-columns-that-are-sometimes-empty表明我应该使用COALESCE()函数在ORDER BY声明的一部分来得到我想要的结果。惊人的！所以我将代码更新为以下内容：
Volunteer.joins(:volunteer_lists).
where("(volunteer_lists.organizer_id = ? AND organizer_type = 'Organization') OR
(volunteer_lists.organizer_id IN (?) AND organizer_type = 'Collaborative')",
self.organization.id, collaboratives).uniq.
.order('COALESCE("volunteers"."first_name", "volunteers"."email") ASC, COALESCE("volunteers"."last_name", "volunteers"."email") ASC, "volunteers"."email" ASC')
问题是这段代码现在返回
PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
Using to_sql在这两个版本的代码中，我发现除了添加了COALESCE()功能。
to_sql原始代码、工作代码：
SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT 1 [["id", 1]]
=> "SELECT DISTINCT \"volunteers\".* FROM \"volunteers\" INNER JOIN \"volunteer_list_connectors\" ON \"volunteer_list_connectors\".\"volunteer_id\" = \"volunteers\".\"id\" INNER JOIN \"volunteer_lists\" ON \"volunteer_lists\".\"id\" = \"volunteer_list_connectors\".\"volunteer_list_id\" WHERE ((volunteer_lists.organizer_id = 1 AND organizer_type = 'Organization') OR\n (volunteer_lists.organizer_id IN (1) AND organizer_type = 'Collaborative')) ORDER BY \"volunteers\".\"first_name\" ASC, \"volunteers\".\"last_name\" ASC, \"volunteers\".\"email\" ASC"
to_sql更新的代码（唯一的区别是之后ORDER BY):
SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT 1 [["id", 1]]
=> "SELECT DISTINCT \"volunteers\".* FROM \"volunteers\" INNER JOIN \"volunteer_list_connectors\" ON \"volunteer_list_connectors\".\"volunteer_id\" = \"volunteers\".\"id\" INNER JOIN \"volunteer_lists\" ON \"volunteer_lists\".\"id\" = \"volunteer_list_connectors\".\"volunteer_list_id\" WHERE ((volunteer_lists.organizer_id = 1 AND organizer_type = 'Organization') OR\n (volunteer_lists.organizer_id IN (1) AND organizer_type = 'Collaborative')) ORDER BY COALESCE(\"volunteers\".\"first_name\", \"volunteers\".\"email\") ASC, COALESCE(\"volunteers\".\"last_name\", \"volunteers\".\"email\") ASC, \"volunteers\".\"email\" ASC"
我测试了尝试我的新代码而没有.uniq（删除DISTINCTsql的一部分），当我这样做时，新代码运行没有错误，但是结果没有正确排序：它们的排序方式与我的原始代码相同（没有错误的代码COALESCE()).
我想象我犯了一个语法错误，但我无法弄清楚它是什么（或者也许我错了并且COALESCE()不是我的问题的正确解决方案）。
任何帮助是极大的赞赏！！
更新与解答
在获得宝贵的帮助后Kristján https://stackoverflow.com/users/802618/kristj%C3%A1n和他的回答如下，我解决了多个问题：
- 当你添加
.uniq对于 ActiveRecord 查询，它添加DISTINCT发送到数据库的 sql。SELECT DISTINCT有一些比简单要求更严格的要求SELECT。正如 Kristján 和本 S.O. 中有更详细的描述。回答 https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by, the DISTINCT表达式必须匹配最左边的ORDER BY表达式。当我更新的时候.order()我的 sql 片段包括COALESCE()，我还需要添加一个匹配的sql片段到SELECT 声明的一部分.select().
- 上面的 1 只是消除了我遇到的错误。此时，我的查询正在运行，但结果的排序方式与使用之前相同
COALESCE()。 Kristján 在下面的回答中提供了正确的描述，但事实证明我的查询运行正确，只是这样COALESCE()将所有大写字母排序在所有小写字母之前。所以“Z”将被排序在“a”之前。这个问题可以通过添加一个函数来解决COALESCE()使用小写字段LOWER().
这是我的回答：
Volunteer.select('LOWER(COALESCE("volunteers"."first_name", "volunteers"."email")), LOWER(COALESCE("volunteers"."last_name", "volunteers"."email")), LOWER("volunteers"."email"), "volunteers".*').
joins(:volunteer_lists).
where("(volunteer_lists.organizer_id = ? AND organizer_type = 'Organization') OR
(volunteer_lists.organizer_id IN (?) AND organizer_type = 'Collaborative')",
self.organization.id, collaboratives).uniq.
order('LOWER(COALESCE("volunteers"."first_name", "volunteers"."email")) ASC, LOWER(COALESCE("volunteers"."last_name", "volunteers"."email")) ASC, LOWER("volunteers"."email") ASC')
Note:
当我稍后打电话时，我上面的回答实际上造成了另一个问题.count关于查询。.count由于习俗而中断.select()我添加的片段。为了解决这个问题，我需要添加一个自定义volunteers_count方法到User没有使用的模型.select()分段。
