I use this query to find comments by a parent id
:
WITH RECURSIVE cte (id, content, path, parent_id, depth) AS (
SELECT id,
content,
array[id] AS path,
parent_id,
1 AS depth
FROM comment
WHERE id = 1
UNION ALL
SELECT comment.id,
comment.content,
cte.path || comment.id,
comment.parent_id,
cte.depth + 1 AS depth
FROM comment
JOIN cte ON comment.parent_id = cte.id
WHERE depth < 3
)
SELECT id, content, path, parent_id, depth FROM cte
ORDER BY path LIMIT 200;
It works well except I'm limiting the depth to 3. How can I find out if the current row has more children and if so get the count of them so I can display the number, ex. "Load X more".
What is the best way to go about this?
In this fiddle, id
4 and 5 have children, so I want to count them.
Best Answer
To count all children, there is no way around following each path to the last element. You can then show rows down to some depth while counting the rest:
SQL fiddle.
path[1:2]
is the array slice from the 1st up to the 2nd element. Details in the manual.This works for a depth of 2. Replace all occurrences of
1:2
with1:3
to do the same for a depth of 3 etc.The main feature is to "group" by the first n elements of the path. Apply the technique laid out in this related question:
The last
ORDER BY
expressionpath <> path[1:2]
produces a boolean result that is only true for the root element of the group (where the whole path is equal to the leading elements), andFALSE
sorts beforeTRUE
. You could simply usepath
instead - not sure which performs better.