CODEWITHSUNDEEP
sqlpostgresqlrecursion
Thomas Price
Thomas Price

Reputation: 101

Recursive query to retrieve all child ids below

I don't think I understand recursive queries well. The classic recursive query I see is the manager and employee recursive query. I'm using that query to capture the entire hierarchy, however I would like to make a query that can enable me to captures all parents, children and their levels (relative to the top level). I feel like I need a recursion on a recursive query?

WITH RECURSIVE
    tree AS (
           SELECT
               id_employee,
               id_manager,
               0 AS level
           FROM
               people
           WHERE
               id_manager = '00001'--this is the boss of all bosses
           UNION ALL
           SELECT
               p.id_employee,
               p.id_manager,
               t.level + 1 AS level
           FROM
               (
               SELECT
                   id_employee,
                   id_manager
               FROM
                   people
               ) p
               JOIN tree t ON p.manager_id = t.employee_id
           )
SELECT *
FROM
    tree;

So right now, since I'm getting the top-level boss 00001, I will get everyone in the organization. But if I add a WHERE id_manager = clause on the last line for someone in the middle of the hierarchy, I'll just get their direct reports. I'd like to retrieve all direct reports below that manager, but also the reports below those managers.

Is there a way to modify this build/modify this query to maintain the entire hierarchy above but also be able to query it to identify someone in the middle of it, all their children and as well as their relative level to the top-level boss?

Thanks!

Upvotes: 1

Views: 1749

Answers (1)

FXD
FXD

Reputation: 2060

Edit: As you commented below, it turned out your problem on the level was what I could not understand.

As you have probably understood, a recursive CTE is divided into 2 parts.

WITH RECURSIVE MyCTE AS (
    <Start of the recursion/loop>
    UNION
    <Step from level N to level N+1/N-1>
)

We will:

  1. Change the starting point of the recursion to start from the correct manager, not the guy on top of the hierarchy.
  2. Browse into the hierarchy the same way you did
  3. Change the loop so that a branch is extended back to the top boss, for the purpose of calculating the level correctly.

Postgresql only allows 1 recursive CTE (not sure for other DBMS), so we will need to do 2 and 3 together. We only need a little extra caution to allow the query to start with several starting point (id_manager IN (...))

WITH RECURSIVE tree(id_root_manager, id_direct_manager, id_employee, level) AS (
    SELECT id_manager,
           id_manager,
           id_employee,
           UNNEST(ARRAY[0,1]) /* level > 0: go to the bottom, level <= 0: go to the top of the hierarchy */
    FROM   people
    WHERE  id_manager IN ('00555')
    UNION ALL
    SELECT id_root_manager,
           id_manager,
           p.id_employee,
           CASE WHEN level <= 0 THEN level-1 ELSE level+1 END
    FROM   people p
    JOIN    tree   t ON (level > 0 AND p.id_manager = t.id_employee) OR (level <= 0 AND t.id_direct_manager = p.id_employee)
)
SELECT id_root_manager, id_direct_manager, id_employee, level - (SELECT MIN(level) FROM tree WHERE id_root_manager = h.id_root_manager) AS level
FROM tree h
WHERE level > 0
ORDER BY id_root_manager, level

If you are not interested in the "root" manager, you may want to avoid duplicates by changing the final select to:

SELECT DISTINCT id_direct_manager, id_employee, level - (SELECT MIN(level) FROM tree WHERE id_root_manager = h.id_root_manager) AS level
FROM tree h
WHERE level > 0
ORDER BY level

Upvotes: 2

Related Questions