Option 1
Include additional columns (just content
in the example) in every iteration of the rCTE right away. You have to GROUP BY
it (them) i the outer SELECT additionally.
Obviously, you don't want to count parents, so join the first level of children right away:
WITH recursive tree AS (
SELECT p.id AS root_id, c.id, p.content
FROM comment p
LEFT JOIN comment c ON c.parent_id = p.id
WHERE p.parent_id IS NULL
UNION ALL
SELECT p.root_id, c.id, p.content
FROM tree p
JOIN comment c ON c.parent_id = p.id
)
SELECT root_id, content, count(id) AS comment_count
FROM tree
GROUP BY 1, 2
ORDER BY 1;
To preserve parents without any children use LEFT JOIN
in the base SELECT
of the rCTE. Consequently, use count(id)
in the outer SELECT
to ignore NULL values produced by this.
Option 2
For very deep trees or big additional columns it may be cheaper to just retrieve IDs in the rCTE and join to the table comment
once more in the outer SELECT
to retrieve more columns:
WITH RECURSIVE tree AS (
SELECT p.id AS root_id, c.id
FROM comment p
LEFT JOIN comment c ON c.parent_id = p.id
WHERE p.parent_id IS NULL
UNION ALL
SELECT p.root_id, c.id
FROM tree p
JOIN comment c ON c.parent_id = p.id
)
SELECT p.*, c.content -- add more columns?
FROM (
SELECT root_id, count(id) AS comment_count
FROM tree
GROUP BY 1 -- cheaper ...
) p
JOIN comment c ON c.id = p.root_id -- ... but additional join
ORDER BY p.root_id;
Your fiddle had two errors. Consider this fixed SQL Fiddle.