'Tree of comments with PGSQL

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)


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source