'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 |
|---|
