Count children of a parent in recursive CTE

Question

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.

Answer 1

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:

WITH RECURSIVE cte AS (
   SELECT id, parent_id, content, array[id] AS path
   FROM   comment
   WHERE  id = 1

   UNION ALL
   SELECT c.id, c.parent_id, c.content, cte.path || c.id
   FROM   comment c
   JOIN   cte ON c.parent_id = cte.id
   )
SELECT DISTINCT ON (path[1:2])
       id, parent_id, content, path
     , count(*) OVER (PARTITION BY path[1:2]) - 1 AS children
FROM   cte
ORDER  BY path[1:2], path <> path[1:2]
LIMIT  200;  -- arbitrary limit, unrelated to the question

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 with 1: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:

• PostgreSQL: running count of rows for a query 'by minute'

The last ORDER BY expression path <> 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), and FALSE sorts before TRUE. You could simply use path instead - not sure which performs better.