PGSQL 的注释树

Question

我的评论树有一个简单的评论示例：

1. comment_id_1
    - comment_id_3
        * commnet_id_5
        * commnet_id_6
    - comment_id_4
        * commnet_id_7
        * commnet_id_8
2. comment_id_2
    - comment_id_9
    - comment_id_10

id | parent_id
---+------------
1  |  null
2  |  null
3  |  1
4  |  1
5  |  3
6  |  3
7  |  4
8  |  4
9  |  2
10 |  2

我想获取给定评论或给定评论列表的所有子评论，从 parent_id = 1 和 content_id = 1 开始>。我进行了查询，但失败了。

WITH RECURSIVE sub_comments AS (
    SELECT com.parent_id, com.id
    FROM comments com
    UNION
    SELECT c.parent_id, c.id
    FROM comments c
         JOIN sub_comments sc ON c.id = sc.parent_id
)
SELECT *
FROM sub_comments
WHERE parent_id IS NOT NULL

我需要添加什么才能仅选择一个（或多个）特定“分支”评论？

我的失败：

WITH RECURSIVE sub_comments AS (
    SELECT com.parent_id, com.id
    FROM comments com
    WHERE com.parent_id = 1
    UNION
    SELECT c.parent_id, c.id
    FROM comments c
         JOIN sub_comments sc ON c.id = sc.parent_id
)
SELECT *
FROM sub_comments
WHERE comment_id IS NOT NULL

替代解决方案是添加这样的限制，但我找不到用条件限制的方法。

WITH RECURSIVE sub_comments AS (
    SELECT com.parent_id, com.id
    FROM comments com
    UNION
    SELECT c.parent_id, c.id
    FROM comments c
         JOIN sub_comments sc ON c.id = sc.parent_id
)
SELECT *
FROM sub_comments
LIMIT 4 (but limit should count only if parent_it IS NULL)

Answer 1

您的第二次尝试很接近，但您翻转了连接条件。查询树时，从根节点开始，然后重复将子节点添加到结果集中，直到结果集中不再有具有父节点但本身尚未包含在结果集中的行。

WITH RECURSIVE sub_comments AS (
    SELECT com.parent_id, com.id
    FROM comments com
    WHERE com.id = 1 -- start with the root itself, not its children
  UNION
    SELECT c.parent_id, c.id
    FROM comments c
    JOIN sub_comments sc ON c.parent_id = sc.id -- or: WHERE c.parent_id IN (SELECT id FROM sub_comments)
)
SELECT *
FROM sub_comments