Rails基于has_many关联membership最新记录的cancelled_at排序问题

你有两个关联模型：

• Customer 和 Membership 为一对多关联，一个客户可拥有多个会员记录

排序规则 #

取每个客户最新创建的Membership记录，按以下规则排序：

1. 若最新会员的cancelled_at为nil（会员活跃），或客户没有任何会员记录，统一排在所有已取消会员的底部，不参与cancelled_at的排序
2. 若客户所有会员均已取消，取最新会员的cancelled_at值进行升/降序排序

你写的查询存在三个核心错误：

1. LEFT JOIN后加了memberships表的WHERE条件，实际等效于内连接，会直接丢失没有任何会员记录的客户（示例中的C、D客户）
2. 子查询逻辑完全错误：你需要取的是最新创建的会员，不是cancelled_at最大的会员，二者逻辑完全不一致，比如客户A有一个旧的已取消会员和新的活跃会员，你的逻辑会拿到旧的已取消记录
3. 排序规则不符合需求：没有区分活跃/已取消客户的排序优先级，也没有按cancelled_at排序
4. 字段拼写错误：表字段是cancelled_at，你写的cancel_at少了字母l，也会导致查询异常

核心思路是先拿到每个客户最新创建的会员记录，再基于该记录的cancelled_at做分层排序：

# 构造子查询：获取每个客户最新创建的会员的取消时间
latest_membership_sql = <<~SQL
  SELECT customer_id, cancelled_at
  FROM memberships m1
  WHERE created_at = (
    SELECT MAX(created_at)
    FROM memberships m2
    WHERE m2.customer_id = m1.customer_id
  )
SQL

# 最终查询
Customer
  .select("customers.*, lm.cancelled_at as latest_membership_cancelled_at")
  .joins("LEFT JOIN (#{latest_membership_sql}) lm ON lm.customer_id = customers.id")
  .order(Arel.sql("CASE WHEN lm.cancelled_at IS NULL THEN 1 ELSE 0 END ASC, lm.cancelled_at ASC, customers.id ASC"))

逻辑说明 #

• 子查询先过滤出每个客户最新创建的那一条会员记录，避免关联多条会员导致数据重复
• LEFT JOIN保留没有任何会员记录的客户，这类客户的latest_membership_cancelled_at默认为NULL
• CASE语句做分层排序：所有cancelled_at为NULL的（活跃会员+无会员客户）统一排到已取消会员后面
• 已取消会员按cancelled_at升序排序，末尾加customers.id排序保证同优先级下结果稳定
• 如果需要按cancelled_at降序排序，只需把lm.cancelled_at ASC改成lm.cancelled_at DESC即可，分层排序规则不变

该查询完全匹配你给出的示例预期输出。

内容的提问来源于stack exchange，提问作者Hemant Patil