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;Nota: Esto ignora cualquier hijo vacío. Puede agregar una combinación izquierda como en la respuesta de @a_horse_with_no_name para obtener esa funcionalidad.
Realmente no puede pasar un parámetro al CTE a menos que se desvíe a los procedimientos almacenados y demás. El CTE es una sola tabla que debe contener todas las filas que desee usar.
Suponiendo un gráfico bastante bueno (sin bordes duplicados, sin ciclos), un código como el siguiente debería hacer lo que está buscando.
WITH RECURSIVE descendants(parent, child) AS ( SELECT * FROM relationships UNION SELECT d.parent, rb FROM descendants d JOIN relationships r ON d.child=ra ) SELECT parent AS n, array_agg(child) AS children FROM descendants GROUP BY parentPara obtener una lista de los hijos de todos los nodos, necesita una combinación izquierda en 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 rb = ca ) select nn, array_agg(rb) filter (where rb is not null) from nodes n left join rels r on r.root = nn group by nn order by nn;