2

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

0

2 Answers 2

2

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.

2
  • The only problem is that its not pulling the content column which is what I was trying to figure out but thank you! Anyway you can edit it?
    – Mike
    Aug 12, 2015 at 19:36
  • @Jake: I added the column. Aug 12, 2015 at 22:27
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;
3
  • Fiddle was down, but I tried your query, doesn't work? sqlfiddle.com/#!15/158ea/24 I'm trying to pull the content column for the parent row
    – Mike
    Aug 11, 2015 at 4:50
  • @Jake Apparently, all is a reserved word; replace it with something else. sqlfiddle.com/#!15/158ea/26/0
    – mustaccio
    Aug 11, 2015 at 11:39
  • The root IDs are those that have no parent – therefore and com.parent_id is null is redundant.
    – Andriy M
    Aug 13, 2015 at 6:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.