Блог

Давайте представим иерархию сотрудников в следующей таблице:

 CREATE TABLE employee(
                        id serial PRIMARY KEY,
                        first_name varchar NOT NULL,
                        manager_id integer,
                        FOREIGN KEY(manager_id) REFERENCES employee(id)
                     );
INSERT INTO employee(first_name, manager_id)
              VALUES('Arden',          null),
                    ('Oliver',         null),
                    ('Luisa',          null),
                    ('Amelia',         null),
                    ('Olivia',         null),
                    ('Lily',              2),
                    ('Ava',               2),
                    ('Isabella',          2),
                    ('Charlie',           2),
                    ('Beatrice',          3),
                    ('Stephanie',         3),
                    ('Emily',             3),
                    ('Mila',              3),
                    ('Isla',              4),
                    ('Ashley',            4),
                    ('James',             7),
                    ('Jack',              7),
                    ('William',           8),
                    ('Harry',             8),
                    ('Robin',             8);
 

Для сотрудника, например, с id = 20, мы можем найти менеджера самого высокого уровня, используя запрос:

 WITH RECURSIVE cte AS
(
   SELECT
      0 cnt, id, first_name, manager_id
   FROM
      employee
   WHERE
      id = 20
   UNION ALL
   SELECT
      cnt 1, employee.id, employee.first_name, employee.manager_id
   FROM
      cte INNER JOIN employee ON cte.manager_id = employee.id
)SELECT * FROM cte WHERE cnt = (SELECT max(cnt) FROM cte);
 

Но мне нужно получить весь список сотрудников — highest_level_manager как показано ниже:

 employee  |  highest_level_manager
----------------------------------
Robin     |    Oliver
Harry     |    Oliver
William   |    Oliver
Jack      |    Oliver
James     |    Oliver
Ashley    |    Amelia
Isla      |    Amelia
Mila      |    Luisa
Emili     |    Luisa
Stephanie |    Luisa
Beatrice  |    Luisa
Charlie   |    Oliver
Isabella  |    Oliver
Ava       |    Oliver
Lily      |    Oliver
Olivia    |    null
Amelia    |    null
Luisa     |    null
Oliver    |    null
Arden     |    null
 

Кто-нибудь знает, как это сделать?

Пройдитесь по дереву для всех сотрудников. Уменьшите рекурсию до ids , добавьте имена в конечный запрос:

 with recursive cte as
(
    select
        id, manager_id, 0 as level
    from
        employee
    union all
    select
        c.id, e.manager_id, level  1
    from cte c
    join employee e on c.manager_id = e.id and e.manager_id is not null
)
select
    e.first_name as employee, 
    m.first_name as highest_level_manager
from (
    select distinct on(id) *
    from cte
    order by id desc, level desc
    ) c
join employee e on c.id = e.id
left join employee m on c.manager_id = m.id
 

Смотрите живую демонстрацию в Db<>fiddle .