sql - Recursive select?

Question

I have the following table structure:

So each forum post has a parent, who also has a parent(except the root posts), etc. What I need is to get the total number of children a forumpost has, including his children's children, grandchildren's children and so on.

For now I have a simple select that returns the immediate children:

select count(*) as child_count 
from forumposts 
where parent_forum_post_id = $criteria.fid

I'm not even sure this is doable via sql, but I'm a begginer in SQL so I thought maybe someone can give some ideas.

Any help is appreciated. Thanks.

Answer 1

This should do it:

with recursive all_posts (id, parentid, root_id) as 
(
  select t1.id, 
         t1.parent_forum_post_id as parentid, 
         t1.id as root_id
  from forumposts t1
  where t1.parent_forum_post_id is null

  union all

  select c1.id, 
         c1.parent_forum_post_id as parentid,
         p.root_id
  from forumposts c1
    join all_posts p on p.id = c1.parent_forum_post_id
)
select root_id, count(*)
from all_posts
order by root_id;

You can change the "starting" point by modifying the condition where t1.parent_forum_post_id is null.