Tengo la siguiente tabla y datos

CREATE TABLE relationships (a TEXT, b TEXT);
CREATE TABLE nodes(n TEXT);
INSERT INTO relationships(a, b) VALUES 
 ('1', '2'), 
 ('1', '3'),
 ('1', '4'),
 ('1', '5'),
 ('2', '6'),
 ('2', '7'),
 ('2', '8'),
 ('3', '9');
INSERT INTO nodes(n) VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('10');

Quiero salir

  n  |  children
  1  |  ['2', '3', '4', '5', '6', '7', '8', '9']
  2  |  ['6', '7', '8', '9']
  3  |  ['9']
  4  |  []
  5  |  []
  6  |  []
  7  |  []
  8  |  []
  9  |  []
  10 |  []

Estoy tratando de usar WITH RECURSIVE pero no sé cómo pasar el parámetro a CTE

WITH RECURSIVE traverse(n) AS (
    SELECT *
    FROM relationships
    WHERE a = n --- not sure how to pass data to here
    UNION ALL
    ...
)
WITH basic_cte AS (
    SELECT a1.n as n,
           (SELECT COALESCE(json_agg(temp), '[]')
            FROM (
                         (SELECT * FROM traverse(a1.a))
                 ) as temp
           ) as children
    FROM nodes as a1
)
SELECT *
FROM basic_cte;
3
Zanko 24 jun. 2020 a las 10:07

2 respuestas

La mejor respuesta

Para obtener una lista de los hijos de todos los nodos, necesita una unión izquierda a la tabla de nodos

with recursive rels as (
  select a,b, a as root
  from relationships
  union all
  select c.*, r.root
  from relationships c
    join rels r on r.b = c.a
)
select n.n, array_agg(r.b) filter (where r.b is not null)
from nodes n
  left join rels r on r.root = n.n
group by n.n
order by n.n;
1
a_horse_with_no_name 24 jun. 2020 a las 07:30

Nota: esto ignora a los hijos vacíos. Puede agregar una combinación izquierda como en @ a_horse_with_no_name's answer para obtener esa funcionalidad.

Realmente no puede pasar un parámetro al CTE a menos que se desvíe de los procedimientos almacenados y demás. El CTE es una tabla única que debe contener todas las filas que desee utilizar.

Suponiendo un gráfico bastante bueno (sin bordes duplicados, sin ciclos), un código como el siguiente debe hacer lo que está buscando.

  • El caso base para la consulta recursiva obtiene todos los descendientes de nivel 1 (los hijos) para todos los nodos que posiblemente podrían ser padres.
  • El paso recursivo recorre el segundo nivel, el tercer nivel, etc. hacia abajo del árbol.
  • Una vez que tengamos todas las tuplas padre-descendiente, podemos agregar los datos según lo desee.
WITH RECURSIVE descendants(parent, child) AS (
    SELECT * FROM relationships
    UNION
    SELECT d.parent, r.b
    FROM descendants d JOIN relationships r ON d.child=r.a
)
SELECT parent AS n, array_agg(child) AS children
FROM descendants
GROUP BY parent
1
Hans Musgrave 24 jun. 2020 a las 08:00