how to use CTE/ recursive with multiple deep employee -> CEO

Question

I have a table that contain employee, manager, ceo, etc

viewer_org_id  | path     |  name    |  id
    1             1          Razor       1
    1             1.2        Michael     2
    2             1.2        Michael     2
    1             1.2.3      Romus       3
    2             1.2.3      Romus       3
    3             1.2.3      Romus       3
    1             1.2.3.4    Lion        4
    2             1.2.3.4    Lion        4
    3             1.2.3.4    Lion        4
    4             1.2.3.4    Lion        4

Razor is a Ceo, Michael is a manager, Romus is employee and Lion is Romus' assistant. the viewer_org_id field is generated by using string_to_aray() of path field, so you can see they are sequence of path-link. The id is id of employee-name.

The output table is

bases  | part_id  |  part_of  |  viewer_org_id   |  name   | id
Razor       1         Razor          1              Razor    1
Razor       1         Razor          1              Michael  2
Michael     2         Michael        2              Michael  2
Razor       1         Razor          1              Romus    3
Razor       1         Michael        2              Romus    3
Michael     2         Michael        2              Romus    3
Romus       3         Romus          3              Romus    3
Razor       1         Razor          1              Lion     4
Razor       1         Michael        2              Lion     4
Michael     2         Michael        2              Lion     4
Razor       1         Romus          3              Lion     4
Michael     2         Romus          3              Lion     4
Romus       3         Romus          3              Lion     4
Lion        4         Lion           4              Lion     4

let's say i have a table employee. i have tried to query by CTE, shown below;

with em
as (select 
           a.name
           b.viewer_org_id,
           b.name as data_org_name,
           b.org_id data_org_id
    from (select distinct name org_id from employee) a
    join (select viewer_org_id, name, org_id from employee) b
         on b.viewer_org_id = a.org_id)
select 
      nn1.name bases,
      nn1.viewer_org_id part_id,
      nn2.name part_of,
      nn2.viewer_org_id,
      nn2.data_org_name name,
      nn2.data_org_id org_id
from em nn1
join em nn2
     on nn1.data_org_id = nn2.viewer_org_id

this query has worked but not optimal, the cost still big enough. Can anyone tell me how to make recursive CTE with 3 level or how to be more effective query? i have tried but there's no way. thank you in advance

I would not use a table format like that, with a path. Check this example: https://www.postgresqltutorial.com/postgresql-recursive-query/ — Frank Heikens, Jan 06 '22 at 15:15
You can use closure table(s) for this purpose, as explained in https://stackoverflow.com/a/38701519/5962802 You will be able to query all subordinates of a CEO no matter the depth, all siblings on a given depth, all chiefs/supervisors above given employee (up to the CEO), etc. — IVO GELOV, Jan 06 '22 at 15:20

score 0 · Answer 1 · answered Jan 06 '22 at 17:37

Maybe using a recursive CTE is a bit overdoing it, if it's only 3 levels deep.

But here's an example of recursion.

with recursive rcte as (
    select base_emp_id, base_org_id, emp_id, org_id, path 
    , 1 as lvl
    from cte
    
    union all
    
    select 
      c.base_emp_id, c.base_org_id
    , t.emp_id, t.org_id, t.path
    , c.lvl+1
    from rcte c
    join employee_relations t
      on c.path = concat(t.path,'.',c.emp_id)
)
, cte as (
    select 
    r1.emp_id as base_emp_id, 
    r1.org_id as base_org_id, 
    r2.emp_id, 
    r2.org_id, 
    r2.path
    from employee_relations r1
    left join employee_relations r2
      on r2.org_id = r1.org_id
     and r1.path = concat(r2.path,'.',r1.emp_id)
), cte2 as (
select 
 c1.base_emp_id as emp1, 
 c1.base_org_id as org1, 
 c1.emp_id as emp2, 
 c1.base_org_id as org2, 
 c2.emp_id as emp3, 
 max(c2.org_id) as org3
from cte c1
left join rcte c2 
  on c2.base_emp_id = c1.base_emp_id
 and c2.base_org_id = c1.base_org_id
 and c2.lvl > 1
group by c1.base_emp_id, c1.base_org_id, c1.emp_id, c2.emp_id
) 
select 
  coalesce(e3.emp_name, e2.emp_name, e1.emp_name) as bases
, coalesce(c.org3, c.org1) as part_id
, coalesce(e2.emp_name, e1.emp_name) as part_of
, c.org1 as viewer_org_id
, e1.emp_name as name   
, e1.emp_id as id
from cte2 c
left join employees e1 on e1.emp_id = c.emp1
left join employees e2 on e2.emp_id = c.emp2
left join employees e3 on e3.emp_id = c.emp3
order by emp1, org1, emp2, org3, emp3

bases   | part_id | part_of | viewer_org_id | name    | id
:------ | ------: | :------ | ------------: | :------ | -:
Razor   |       1 | Razor   |             1 | Razor   |  1
Razor   |       1 | Razor   |             1 | Michael |  2
Michael |       2 | Michael |             2 | Michael |  2
Razor   |       1 | Michael |             1 | Romus   |  3
Razor   |       1 | Michael |             2 | Romus   |  3
Romus   |       3 | Romus   |             3 | Romus   |  3
Razor   |       1 | Romus   |             1 | Lion    |  4
Michael |       2 | Romus   |             1 | Lion    |  4
Razor   |       1 | Romus   |             2 | Lion    |  4
Michael |       2 | Romus   |             2 | Lion    |  4
Razor   |       1 | Romus   |             3 | Lion    |  4
Michael |       2 | Romus   |             3 | Lion    |  4
Lion    |       4 | Lion    |             4 | Lion    |  4

db<>fiddle here

how to use CTE/ recursive with multiple deep employee -> CEO

1 Answers1