Recursive choice?

Question

I have the following table structure:

So, each forum has a parent, which also has a parent (except for root messages), etc. I need the total number of children to be onumpost, including his children, children, children of grandchildren, etc.

Now I have a simple choice that returns immediate children:

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

I'm not even sure if this is doable via sql, but I'm starting in SQL, so I thought maybe someone could 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 changing the condition where t1.parent_forum_post_id is null .

Answer 2

Have you tried Recursive queries using common table expressions

Answer 3

This is WITH RECURSIVE in Postgresql

Answer 4

 WITH RecursiveCte AS ( SELECT 1 AS LEVEL, H1.intUserId, H1.intReportsTo, H1.strUserName FROM mstUsers H1 WHERE id = @intUserId UNION ALL SELECT RCTE.level + 1 AS LEVEL, H2.intUserId, H2.intReportsTo, H2.strUserName FROM mstUsers H2 INNER JOIN RecursiveCte RCTE ON H2.intReportsTo = RCTE. ) SELECT intUserId,strUserName,LEVEL FROM RecursiveCte