I use this query to find comments by a parent
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.