表自连接后子评论以列而非行展示的问题解决

给定如下评论表结构，子评论无法作为父评论：

CREATE TABLE comments (
    id uuid PRIMARY KEY,
    parent_id uuid REFERENCES comments(id),
    user_id uuid NOT NULL REFERENCES users(id),
    text text NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone,
    deleted_at timestamp with time zone
);

需求 #

• 按降序展示5条最新的父评论
• 每条父评论下的所有子评论按升序展示
• 支持分页获取下一批5条父评论

问题现状 #

之前尝试的自连接方式会将子评论作为附加列与父评论同行，期望子评论以独立行展示。

解决方案 #

使用UNION ALL合并父评论与对应子评论为独立行，通过排序规则保证父评论在前、子评论按时间升序，同时支持分页：

WITH paginated_parents AS (
    SELECT 
        id, 
        parent_id, 
        text, 
        user_id, 
        created_at, 
        updated_at, 
        deleted_at,
        created_at AS parent_created_at -- 用于父评论组的排序依据
    FROM comments
    WHERE parent_id IS NULL
    ORDER BY created_at DESC
    LIMIT 5 -- 每页5条父评论
    OFFSET 0 -- 分页偏移：第1页设为0，第2页设为5，以此类推
)
-- 选取当前分页的父评论
SELECT 
    id, 
    parent_id, 
    text, 
    user_id, 
    created_at, 
    updated_at, 
    deleted_at
FROM paginated_parents
UNION ALL
-- 选取对应父评论下的所有子评论
SELECT 
    c.id, 
    c.parent_id, 
    c.text, 
    c.user_id, 
    c.created_at, 
    c.updated_at, 
    c.deleted_at
FROM comments c
JOIN paginated_parents pp ON c.parent_id = pp.id
ORDER BY 
    pp.parent_created_at DESC, -- 保持父评论组的降序顺序
    CASE WHEN c.parent_id IS NULL THEN 0 ELSE 1 END, -- 父评论排在其子评论之前
    c.created_at ASC; -- 子评论按创建时间升序排列

说明 #

1. 分页控制：修改OFFSET值实现分页，例如第2页设置OFFSET 5，第3页设置OFFSET 10。
2. 排序逻辑：
   • 优先按父评论的创建时间降序，确保最新的父评论组排在最前
   • 通过CASE表达式强制父评论在其子评论之前展示
   • 子评论内部按创建时间升序，实现最早的子评论先显示
3. 独立行展示：UNION ALL将父评论和子评论分别作为独立行返回，完全符合预期输出格式。

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