Tree of comments with PGSQL-CodePudding

There is simply example of comments for my comments tree:

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

And I want to get all sub-comments of given comment or given comments list, starts from parent_id = 1 and content_id = 1. I made query but it fails.

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

What do I need to add to select only one (or more) particulary "branch" of comments?

My fails:

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

Alternate solution is to add such of limit but I coludn't find way to limit with condition.

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)

CodePudding user response：

Your second attempt comes close, but you flipped the join condition. When querying for the tree, you start with the root node and then repeatedly add children to the result set, until there are no rows left that have a parent in the result set but are not yet in the result set themselves.

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