Рекурсивная задача запроса - простой пример родителя/ребенка

Ответ 1

С помощью RhodiumToad на #postgresql я пришел к этому решению:

WITH RECURSIVE node_graph AS (
    SELECT ancestor_node_id as path_start, descendant_node_id as path_end,
           array[ancestor_node_id, descendant_node_id] as path 
    FROM node_relations

    UNION ALL 

    SELECT ng.path_start, nr.descendant_node_id as path_end,
           ng.path || nr.descendant_node_id as path
    FROM node_graph ng
    JOIN node_relations nr ON ng.path_end = nr.ancestor_node_id
) 
SELECT * from node_graph order by path_start, array_length(path,1);

Результат будет точно таким, как ожидалось.

Ответ 2

Альтернативный подход состоял бы в том, чтобы пересечь график в обратном порядке:

WITH RECURSIVE cte AS (
   SELECT array[r.ancestor_node_id, r.descendant_node_id] AS path
   FROM   node_relations r
   LEFT   JOIN node_relations r0 ON r0.ancestor_node_id = r.descendant_node_id
   WHERE  r0.ancestor_node_id IS NULL  -- start at the end

   UNION ALL 
   SELECT r.ancestor_node_id || c.path
   FROM   cte c
   JOIN   node_relations r ON r.descendant_node_id = c.path[1]
   ) 
SELECT path
FROM   cte
ORDER  BY path;

Это создает подмножество с каждым путем от каждого корня node до его конечного потомка. Для глубоких деревьев, которые также распространяются много, это повлечет за собой гораздо меньшее количество операций объединения. Чтобы дополнительно добавить каждый подпуть, вы можете добавить LATERAL join к внешнему SELECT:

WITH RECURSIVE cte AS (
   SELECT array[r.ancestor_node_id, r.descendant_node_id] AS path
   FROM   node_relations r
   LEFT   JOIN node_relations r0 ON r0.ancestor_node_id = r.descendant_node_id
   WHERE  r0.ancestor_node_id IS NULL  -- start at the end

   UNION ALL 
   SELECT r.ancestor_node_id || c.path
   FROM   cte c
   JOIN   node_relations r ON r.descendant_node_id = c.path[1]
   ) 
SELECT l.path
FROM   cte, LATERAL (
   SELECT path[1:g] AS path
   FROM   generate_series(2, array_length(path,1)) g
   ) l
ORDER  BY l.path;

Я провел быстрый тест, но он не работал быстрее, чем решение RhodiumToad. Это может быть быстрее для больших или больших таблиц. Попробуйте свои данные.

Ответ 3

Я вижу две проблемы с запросом:

• нерекурсивная часть не определяет корень node. Вам нужно либо явно выбрать это, используя WHERE descendant_node_id = 14 или "динамически", используя:

  SELECT ..
  FROM   node_relations nr1
  WHERE  NOT EXISTS (SELECT 1
                     FROM node_relations nr2
                     WHERE nr2.ancestor_node_id = nr1.descendant_node_id)
  

• с правильной начальной точкой, путь не завершен, так как он пропустит окончательный node во время агрегации в рекурсивной части. Поэтому во внешнем запросе вам нужно добавить ancestor_node_id к сгенерированному пути.

Таким образом, запрос будет выглядеть так:

WITH RECURSIVE node_graph AS (
   SELECT nr1.id, nr1.ancestor_node_id, nr1.descendant_node_id, ARRAY[nr1.descendant_node_id] AS path, 0 as level
   FROM   node_relations nr1
   WHERE  NOT EXISTS (SELECT 1
                      FROM node_relations nr2
                      WHERE nr2.ancestor_node_id = nr1.descendant_node_id)

   UNION  ALL

   SELECT nr.id, nr.ancestor_node_id, nr.descendant_node_id, nr.descendant_node_id || ng.path, ng.level + 1 as level
   FROM node_relations nr
     JOIN node_graph ng ON ng.ancestor_node_id = nr.descendant_node_id

)
SELECT ancestor_node_id||path as path, -- add the last element of the sub-tree to the path
       level as depth
FROM   node_graph
ORDER  BY level

Вот SQLFiddle: http://sqlfiddle.com/#!15/e646b/3