I've a table with some ID's of a tree structured like this:
term1_id term2_id
1507 ;2
107 ;3
1 ;5
5 ;6
5 ;7
812 ;8
14 ;9
2719 ;10
9 ;11
9 ;12
This query teoretically extracts the path i need putting everything in an array:
WITH RECURSIVE cte(path) AS ( --Recursively call on path (Works for every length)
SELECT array[r.term1_id, r.term2_id] AS path
FROM temp_table r
LEFT JOIN temp_table r0 ON r0.term1_id = r.term2_id
WHERE r0.term2_id IS NULL
UNION ALL
SELECT r.term1_id || c.path
FROM cte c
JOIN temp_table r ON r.term2_id = c.path[1]
),
max_len AS (
SELECT max(array_length(path, 1)) max_len
FROM cte
)
select array_to_string(fill_with_last_element(path, 15), ',' , '*')
from cte
But the problem is that it grabs the correct path but even the "subpath"
E.G.
path: {118;119;79;10935;77;12210;107;3}
subpath: {12210;107;3;3;3;3;3;3}
subpath: {10935;77;12210;107;3;3;3;3}
I can have only on father but father can have multiple sons
How can i avoid the creation of subpath?