Retrieve additional columns in recursive CTE

Question

This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep.

I manage to do it with two separate queries but there has to be a way to just use one and get the count of the children:

with recursive all_comments as (
   select id, parent_id, id as root_id
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from comment c
     join all_comments p on c.parent_id = p.id
)
select root_id, count(*) as comment_count
from all_comments
group by root_id;

How would I pull content column from the parent comment in this fiddle?

http://sqlfiddle.com/#!15/158ea/15

Answer 1

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.

Answer 2

Your fiddle does not open, so I'm not sure if I understand correctly what you have and what you want, but I'd venture to guess you need to join back to comment on id, something like this:

with recursive all_comments as (
   select id, parent_id, id as root_id
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from comment c
     join all_comments p on c.parent_id = p.id
)
select a.root_id, a.comment_count, com.content from 
(select root_id, count(*) as comment_count
from all_comments
group by root_id) as a
inner join
comment as com
 on a.root_id = com.id and com.parent_id is null;