Recursive select?
postgresql, sql
Solution
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`.
Problem
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.