CODEWITHSUNDEEP
sqlpostgresqlrecursive-query
Fofole
Fofole

Reputation: 3548

Getting leaf level recursive select

I have a table forumposts with id,parent_forum_post_id and for a given id=1221 I found it's children count.

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

    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      (count(*)-1) as child_count
from        all_posts
where       root_id=1221
group by    root_id;

What I need now, is the exact opposite: for a given id, to find out it's level, which is determined by his number of parents(it's parent, and it's parent's parent until it finds null in it's parent parent_forum_post_id column). Hope this makes sense.

Any help is appreciated. Thanks.

Upvotes: 2

Views: 2635

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656942

This query can be largely simplified to:

WITH RECURSIVE p AS (
    SELECT parent_forum_post_id AS p_id
    FROM   forumposts
    WHERE  id = 1221   

    UNION ALL
    SELECT f.parent_forum_post_id
    FROM   p
    JOIN   forumposts f ON f.id = p.p_id
    )
SELECT count(*) AS level
FROM   posts;

Should be considerably faster, too.

Upvotes: 3

weenoid
weenoid

Reputation: 1186

If i've understood correctly you want the hierarchy depth of a particular node given its id (root being level 1).This is for postgresql:

with recursive all_posts (id, parentid, node_id) as (
    select  t1.id,
            t1.parent_forum_post_id as parentid,
            t1.id as node_id
    from    forumposts t1

    union all

    select  c1.id,
            c1.parent_forum_post_id as parentid,
            p.node_id
    from    forumposts c1
    join    all_posts p on p.parentid = c1.id
)
select      count(*) as level
from        all_posts
where       node_id=1221   
group by    node_id;

Upvotes: 2

MatBailie
MatBailie

Reputation: 86735

WITH recursive
  anticendent
AS
(
  SELECT
    id                       AS post_id,
    parent_forum_post_id     AS anticendent_post_id,
    1                        AS distance
  FROM
    forumposts

  UNION ALL

  SELECT
    anticendent.post_id,
    forumposts.parent_forum_post_id,
    distance + 1
  FROM
    anticendent
  INNER JOIN
    forumposts
      ON forumposts.id = anticendent.anticendent_post_id
)

SELECT
  post_id,
  MAX(distance)  AS level
FROM
  anticendent
GROUP BY
  post_id
WHERE
  post_id = 1221

Or...

SELECT
  *
FROM
  anticendent
WHERE
  post_id = 1221
  AND anticendent_post_id IS NULL

Upvotes: 2

Related Questions