Как рекурсивно(SQL) вывести parent и child?

Question

Имеется следующая SQL-команда:

        WITH RECURSIVE menu AS (
            SELECT id, parent_id, name, CAST(name AS CHAR(500)) AS path
            FROM app_menu 
            WHERE name = %s

        UNION 

        SELECT app_menu.id, app_menu.parent_id, app_menu.name, menu.path || app_menu.name
        FROM app_menu
            JOIN menu 
                ON app_menu.parent_id = menu.id)   

        SELECT * FROM menu ORDER BY path;'''

Здесь я вывожу в иерархическом порядке только детей объекта. Как мне вывести родителей тоже. У меня получается бесконечный цикл, если писать:

ON app_menu.parent_id = menu.id OR app_menu.id= menu.parent_id

Мне нужно уложиться в один запрос в SQL

Answer 1

Предложу такой вариант набора пути из иерархии (parent-child):
Тестовый пример

create table app_menu(id int,parent_id int,name varchar(20));
insert into app_menu values
 (1,0,'Files')
  ,(2,1,'Open')
    ,(21,2,'Recent')
    ,(22,2,'Favorites')
       ,(23,22,'Excel files')
       ,(24,22,'Word files')
         ,(25,24,'Word templates')
  ,(3,1,'Print')
    ,(31,3,'Print HP')
    ,(32,3,'Print .PDF')
  ,(4,1,'Close');

WITH RECURSIVE menu AS (
  SELECT id
     , parent_id
     , name
     , CAST(name AS varCHAR(500)) AS path
     ,id as sId ,1 as lvl
  FROM app_menu 
  WHERE name ='Open' -- %s

UNION all

  SELECT case when app_menu.id=menu.parent_id then menu.Id 
         else app_menu.id end as id 
     ,case when app_menu.id=menu.parent_id then app_menu.parent_Id 
      else menu.parent_id 
      end as parent_id
     ,app_menu.name
     ,case when app_menu.id=menu.parent_id then
          cast(app_menu.name || '-' || menu.path as varchar(500))
      else
          cast(menu.path || '-' || app_menu.name as varchar(500))
      end path
     ,menu.sId ,menu.lvl+1 as lvl
  FROM app_menu
  JOIN menu 
      ON  ( app_menu.id = menu.parent_id)
       or (menu.parent_id=0 and  app_menu.parent_id = menu.id)
  where lvl<6 --это только для отладки
  )   

SELECT * FROM menu ORDER BY path;

Результат запроса на тестовых данных

id	parent_id	name	path	sId	lvl
2	0	Files	Files-Open	2	2
22	0	Favorites	Files-Open-Favorites	2	3
23	0	Excel files	Files-Open-Favorites-Excel files	2	4
24	0	Word files	Files-Open-Favorites-Word files	2	4
25	0	Word templates	Files-Open-Favorites-Word files-Word templates	2	5
21	0	Recent	Files-Open-Recent	2	3
2	1	Open	Open	2	1

Тот же запрос, если в качестве начального задать пункт 'Favorites'

id	parent_id	name	path	sId	lvl
22	2	Favorites	Favorites	22	1
22	0	Files	Files-Open-Favorites	22	3
23	0	Excel files	Files-Open-Favorites-Excel files	22	4
24	0	Word files	Files-Open-Favorites-Word files	22	4
25	0	Word templates	Files-Open-Favorites-Word files-Word templates	22	5
22	1	Open	Open-Favorites	22	2

Пример здесь
Выглядит несколько заморочено, но общая идея такая: В рекурсии присоединяем к пути слева и потом справа. При этом текущий элемент имеет Id самого правого из текущего пути, parent_id самого левого из текущего пути.
Было бы интересно посмотреть комментарий @Akina на данное решение и более подробное пояснение описанного им подхода к решению данной задачи.

БЛОГ НА HUSL

Как рекурсивно(SQL) вывести parent и child?

Ответы (1 шт):