Rails基于最新的created_at记录对列进行排序

Question

我有两个模型，分别命名为" customer "one_answers" membership "。客户可以拥有多个会员资格。我想根据成员的created_at和cancelled_at日期列对记录进行排序。成员记录应该是最近创建的记录，取消的记录不应该包含nil值。例如，如果id为1的客户有两个成员记录，其中created_at为2020年12月25日和2020年12月27日，取消日期为2021年1月1日和nil，那么这个记录应该去底部，因为最新的成员资格仍然是活跃的，因为它的cancelled_at日期为nil，不应该出现在asc或desc排序列表中。但是，如果客户有两个会员资格，其中created_at为2020年12月25日和2020年12月27日，取消日期为2000年1月1日和2000年1月5日，则应该弹出一条取消日期为2000年1月5日的记录。

Database Table - Customer
Id      Name
1        A
2        B
3        C
4        D
5        E
Database Table - Membership
id   customer_id   created_at    cancelled_at
1       1           1 jan 2000     5 jan 2000
2       1           2 jan 2000     nil
3       2           1 Dec 1999     2 Dec 1999
4       5           15 Jan 2000    16 Jan 2000
5       5           17 Jan 2000    20 Jan 2000
Then result should be (for asc order of cancelled_at)
customer_id name cancelled at    
2       B      2 Dec 1999
5       E      20 Jan 2000
1       A      nil
3       C      nil
4       D      nil

我的查询没有产生期望的输出:

Customer.joins('LEFT JOIN memberships')
.where("memberships.cancel_at = (SELECT MAX(memberships.cancel_at) FROM memberships WHERE customers.id = memberships.customer_id)")
.group('customers.id')
.order('MAX(memberships.created_at) ASC')

Answer 1

解决这个问题的一个非常有效的方法是使用横向连接:

SELECT c.*, latest_membership.cancelled_at AS cancelled_at 
FROM customers c
LEFT JOIN LATERAL (
SELECT cancelled_at
FROM memberships m
WHERE m.customer_id = c.id -- lateral reference
ORDER BY m.created_at
LIMIT 1
) AS latest_membership ON TRUE
ORDER BY latest_membership.cancelled_at ASC NULLS LAST

这个花哨的新玩具可能有点难以理解，但它基本上就像foreach循环-除了在SQL中。PostgreSQL将遍历结果集中的每一行，并使用该行作为参数计算子查询。疯狂的东西!

ActiveRecord不支持横向连接"开箱即用";因为这是Postgres特有的功能，它的目标是多语言。但是您可以使用SQL字符串或Arel创建它们。

Customer
.select(
'customers.*', 
'latest_membership.cancelled_at AS cancelled_at'
)
.joins(<<~SQL)
LEFT JOIN LATERAL (
SELECT cancelled_at
FROM memberships m
WHERE m.customer_id = c.id -- lateral reference
ORDER created_at
LIMIT 1
) AS latest_membership ON TRUE
SQL
.order('latest_membership.cancelled_at ASC NULLS LAST')