I have a non-binary tree of customer, and I need to obtain all the IDs in a tree for the given node.
The table is very simple, just an join table with a parent id and child id. This is a representation of the tree I stored in my db.
In this example if I search for node 17 I need in return 14-17. If I search for 11 I need in return 1-6-5-4-8-11-12-7-2-10-3.
The order is not important. I only need the ID to avoid circularity when adding children to a node.
I created this query. The ancestor part works fine, I retrieve all parent nodes, but for the descendants I have some trouble. I'm only able to retrieve some part of the tree. For example, with node 11 I retrieve 4-10-6-11-7-8, so all right part of the tree is missing.
WITH RECURSIVE
-- starting node(s)
starting (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.child = :node or t.parent = :node
)
,
ancestors (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.parent IN (SELECT parent FROM starting)
UNION ALL
SELECT t.parent, t.child
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (parent, child) AS
(
SELECT t.parent, t.child
FROM public.customerincustomer AS t
WHERE t.parent IN (SELECT parent FROM starting) or t.child in (select child from starting)
UNION ALL
SELECT t.parent, t.child
FROM public.customerincustomer AS t JOIN ancestors AS a ON t.parent = a.child
)
table ancestors
union all
table descendants
UPDATE
I see that many examples included in the tree table also the root in form (root_id, null).
In my case I don't have this record.
For example, taking the smallest tree 14->17, in my table I have only one record parent, child
14 17
ltree