PostgreSQL自引用评论表：按回复时间排序指定用户回复过的根评论

通用SQL方案 #

要实现需求，核心是先获取用户对每个根评论的最新回复时间，再关联根评论表并按该时间倒序排序。这样既保证根评论唯一，又能满足排序要求：

-- 替换1为目标用户的group_member_id
SELECT c.*, lr.max_reply_time
FROM comments c
JOIN (
  -- 子查询：按根评论ID分组，获取用户对每个根评论的最新回复时间
  SELECT 
    parent_id, 
    MAX(created_at) AS max_reply_time
  FROM comments
  WHERE 
    group_member_id = 1 
    AND parent_id IS NOT NULL
  GROUP BY parent_id
) lr ON c.id = lr.parent_id
-- 按用户的最新回复时间倒序排列
ORDER BY lr.max_reply_time DESC;

逻辑说明 #

1. 子查询部分：筛选出目标用户的所有回复（parent_id IS NOT NULL），按根评论的ID（parent_id）分组，取每组的最大created_at（即用户对该根评论的最新回复时间）。
2. 关联根评论：将子查询结果与comments表关联，关联条件是根评论的id等于子查询的parent_id（根评论的parent_id本身为NULL）。
3. 排序：最终按子查询得到的max_reply_time倒序，确保最新回复的根评论排在最前面。

对比你之前的查询：PostgreSQL的DISTINCT ON要求ORDER BY的第一个字段必须是DISTINCT ON指定的字段，所以会强制按parent_id排序，无法直接实现按回复时间排序。而GROUP BY的方式可以先获取每个根评论的关键排序字段，再关联排序，更符合需求。

Rails Active Record/Arel实现方案 #

假设你的Comment模型已配置自关联：

class Comment < ApplicationRecord
  belongs_to :group_member
  belongs_to :parent, class_name: "Comment", optional: true
  has_many :replies, class_name: "Comment", foreign_key: "parent_id"
end

方案1：基于SQL字符串的Active Record查询 #

target_member_id = 1 # 替换为目标用户ID

# 构建子查询：获取用户对每个根评论的最新回复时间
latest_replies = Comment.select(:parent_id, "MAX(created_at) AS max_reply_time")
                        .where(group_member_id: target_member_id, parent_id: nil.not)
                        .group(:parent_id)

# 关联根评论并排序
root_comments = Comment.joins("JOIN (#{latest_replies.to_sql}) lr ON comments.id = lr.parent_id")
                       .select("comments.*, lr.max_reply_time")
                       .order("lr.max_reply_time DESC")

方案2：纯Arel实现（更安全，避免SQL注入风险） #

target_member_id = 1
comments_table = Comment.arel_table

# 构建子查询
latest_replies_subquery = comments_table.project(
  comments_table[:parent_id],
  comments_table[:created_at].maximum.as("max_reply_time")
).where(
  comments_table[:group_member_id].eq(target_member_id)
   .and(comments_table[:parent_id].not_eq(nil))
).group(comments_table[:parent_id])

# 关联并查询根评论
root_comments = Comment.joins(
  comments_table.join(latest_replies_subquery, Arel::Nodes::InnerJoin)
                .on(comments_table[:id].eq(latest_replies_subquery[:parent_id]))
                .join_sources
).select("comments.*, max_reply_time")
 .order("max_reply_time DESC")

结果验证 #

以Johnny Tables（group_member_id=1）为例，最终查询结果会按他的回复时间倒序返回根评论：

• 根评论ID=2（最新回复时间2023-08-01 12:00:07）
• 根评论ID=1（最新回复时间2023-08-01 12:00:05）
• 根评论ID=3（最新回复时间2023-08-01 12:00:03）

内容的提问来源于stack exchange，提问作者Michael Bester