2

I'm trying to get the count of child of a parent row. The sql query I'm using as follow only pulls the immediate count, how can I get total count as childs can have more childs.

select  c.id, c.base32_id, c.author, c.message, c.title, c.type, c.datetime,
(select count(*) from comment where parent_id= c.id ) as ChildCount
from comment c where c.parent_id is null order by id asc limit 25  

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

1

You need to first retrieve the whole tree of comments using a recursive common table expression. Then you can count the number of children for each root node:

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;

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

If you don't want to get the count per "thread" but for each node, you can use something like this:

with recursive all_comments as (
   select id, parent_id, id as root_id, 1 as level
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id, p.level + 1
   from comment c
     join all_comments p on c.parent_id = p.id
)
select ac.id, 
       ac.parent_id,
       ac.root_id,
       count(*) over (partition by ac.root_id) - count(*) over (partition by ac.root_id order by level) as child_count
from all_comments ac
order by ac.id;

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

The above is using window functions to calculate the total number or nodes for a root node and the number of nodes in each level:

count(*) over (partition by ac.root_id) counts the number of children (including the root) for each root node.

count(*) over (partition by ac.root_id order by level) counts the number of nodes up to the current's node level. The difference between the total count and the ones "till now" is the number of child nodes.


Btw: comment is a SQL keyword, you shouldn't use it as a table name.

  • Thank you that works! One question for the first query how would I put columns from the parent, such as 'title'? – user3674136 Aug 9 '15 at 19:54
0

I not see exact what you will achieve, but here is a try:

select  parent_id , count(id) as comment from comment
group by parent_id
order by parent_id

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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