使用Active Record&有时会为空的ORDER BY列滑轨 [英] ORDER BY columns that are sometimes empty using Active Record & Rails

问题描述

在我的Rails应用程序中(使用postgresql)，我试图组成一个Active Record查询来查找一组志愿者记录，然后按first_name，然后依次按last_name和email的顺序对其进行排序.此外，first_name和last_name可能是null(两者都将是null或两者都不是null).例如，我希望对下面的列表进行排序:

In my rails app (using postgresql), I'm trying to compose an Active Record query to find a group of volunteer records and then order them by first_name, then last_name, then email. Additionally, first_name and last_name may be null (either both will be null or both will be not null). For example, I would want to following list to be sorted thusly:

1. 志愿者[名字:'Alex'，姓氏:'Diego'，电子邮件:'[e-mail]']
2. 志愿者[名字:null，姓氏:null，电子邮件:'[e-mail]']
3. 志愿者[名字:'Josh'，姓氏:'Broger'，电子邮件:'[e-mail]']
4. 志愿者[名字:'Josh'，姓氏:'Broger'，电子邮件:'[e-mail]']
5. 志愿者[名字:'Josh'，姓氏:'Kenton'，电子邮件:'[e-mail]']

1. Volunteer [first_name: 'Alex', last_name: 'Diego', email: '[e-mail]']
2. Volunteer [first_name: null, last_name: null, email: '[e-mail]']
3. Volunteer [first_name: 'Josh', last_name: 'Broger', email: '[e-mail]']
4. Volunteer [first_name: 'Josh', last_name: 'Broger', email: '[e-mail]']
5. Volunteer [first_name: 'Josh', last_name: 'Kenton', email: '[e-mail]']

最初，我有以下代码:

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志愿者将是最后一名). 这篇有用的帖子的答案表明我应该使用语句的ORDER BY部分中的COALESCE()函数可获取所需的结果.惊人的！因此，我将代码更新为以下内容:

This code works except the results are grouped by volunteers with first_name & last_name first, other volunteers with only email last (so in the example list above, volunteer #2 would be last). The answer to this helpful post indicates that I should use a COALESCE() function in the ORDER BY part of the statement to get the results I want. Awesome! So I updated my code to the following:

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

问题在于此代码现在返回

The problem is that this code now returns

PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

在两个版本的代码上使用to_sql，我发现它们完全相同，只是添加了COALESCE()函数.

Using to_sql on both versions of the code, I find they are exactly the same except for the addition of the COALESCE() function.

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之后):

to_sql of updated code (the only difference is after 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的新代码(以删除sql的DISTINCT部分)，当我执行此操作时，新代码将正常运行，但是结果未正确排序:它们的排序方式相同就像我的原始代码一样(没有COALESCE()的代码).

I tested trying my new code without .uniq (to remove the DISTINCT part of the sql) and when I do this the new code runs without error, however the results are NOT sorted properly: they are sorted the same way as my original code is (the code without COALESCE()).

我想象我犯了一个语法错误，但是我无法弄清楚它是什么(或者我错了，COALESCE()不是我的问题的正确解决方案).

I imagine that there is a syntax error that I've committed, but I can't figure out what it is (or perhaps I'm wrong and COALESCE() is not the proper solution to my problem).

我们非常感谢您的帮助！

Any help is GREATLY appreciated!!

在获得Kristján的宝贵帮助后，我解决了以下问题:问题:

After getting invaluable help from Kristján and his answer below, I solved what turned out to be multiple problems:

1. 将.uniq添加到ActiveRecord查询时，会将DISTINCT添加到要发送到数据库的sql. SELECT DISTINCT的要求比简单地SELECT更为严格.正如Kristján和所指出的那样，在本S.O.中有更详细的描述.答案，DISTINCT表达式必须与最左边的ORDER BY表达式匹配.当我用包含COALESCE()的sql片段更新.order()时，我还需要使用.select()将匹配的sql片段添加到语句的SELECT 部分.
上面的
2. 1只是删除了我遇到的错误.那时，我的查询正在运行，但是结果的排序方式与使用COALESCE()之前的排序方式相同. Kristján在下面的回答中提供了适当的描述，但事实证明我的查询运行正确，只是COALESCE()将任何大写字母排在小写字母之前.因此，"Z"表示将在"a"之前排序.可以通过添加使用LOWER()将COALESCE()字段转换为小写字母的函数来解决此问题.

1. When you add .uniq to an ActiveRecord query, it adds DISTINCT to the sql that gets sent to the database. SELECT DISTINCT has some stricter requirements than simply SELECT. As pointed out by Kristján and described in more detail in this S.O. answer, the DISTINCT expression(s) must match the leftmost ORDER BY expression(s). When I updated .order() with my sql fragment including COALESCE(), I also needed to add a matching sql fragment to the SELECT part of the statement with .select().
2. 1 above just removes the error I was getting. At that point, my query was running but the results were being sorted the same as they were before using COALESCE(). Kristján provides a proper description in his answer below, but turns out my query was running correctly, its just that COALESCE() sorts anything uppercase before anything lowercase. so "Z" will be sorted in front of "a". This problem can be solved by adding a function to convert the COALESCE() fields to lowercase using 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')

注意:

当我稍后在查询中调用.count时，上述答案实际上创建了另一个问题.由于我添加了自定义.select()片段，导致.count中断.为了解决这个问题，我需要在不使用.select()片段的User模型中添加自定义volunteers_count方法.

My answer above actually created another problem when I later call .count on the query. .count breaks because of the custom .select() fragment I've added. To solve this, I needed to add a custom volunteers_count method to the User model that didn't make use of the .select() fragment.

推荐答案

您遇到了字母大小写问题:您的姓名全部大写，但电子邮件为小写，并且在大多数归类中，大写字母先于小写.看看这个简单的例子:

You're running in to a letter case problem: Your names are all capitalized, but the emails are lowercase, and with most collations, uppercase letters come before lowercase. Check out this trivial example:

#= select * from (values ('b'), ('B'), ('a'), ('A')) t (letter);
 letter
--------
 b
 B
 a
 A

#= select * from (values ('b'), ('B'), ('a'), ('A')) t (letter) order by letter;
 letter
--------
 A
 B
 a
 b

因此您的查询实际上运行良好，只是[e-mail]在Josh之后排序.为避免这种情况，您可以按小写值排序.这是您拥有的数据的简单版本:

So your query is actually working perfectly, it's just that [e-mail] sorts after Josh. To avoid this, you can sort by the lowercase value. Here's a simple version of the data you have:

#= select * from volunteers;
 first_name | last_name |       email
------------+-----------+--------------------
 Josh       | Broger    | [e-mail]
 Josh       | Kenton    | [e-mail]
 ∅          | ∅         | [e-mail]
 Josh       | Broger    | [e-mail]
 Alex       | Diego     | [e-mail]

然后使用您要使用的coalesce进行排序:

Then to sort using the coalesce you're after:

#= select * from volunteers order by lower(coalesce(first_name, email));
 first_name | last_name |       email
------------+-----------+--------------------
 Alex       | Diego     | [e-mail]
 ∅          | ∅         | [e-mail]
 Josh       | Broger    | [e-mail]
 Josh       | Broger    | [e-mail]
 Josh       | Kenton    | [e-mail]

或使用ActiveRecord的完整版本:

Volunteer
  .joins(:volunteer_lists)
  .where(
    "(volunteer_lists.organizer_id = ? AND organizer_type = 'Organization') OR (volunteer_lists.organizer_id IN (?) AND organizer_type = 'Collaborative')",
    organization.id, collaboratives
  )
  .order('LOWER(COALESCE("volunteers"."first_name", "volunteers"."last_name", "volunteers"."email"))')

这篇关于使用Active Record&有时会为空的ORDER BY列滑轨的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！