I have a leaf node in a tree structure and I need a Postgres query to recursively traverse the parent nodes to get me the full path to my leaf node.
My table looks like this – the problem being there is no specific root node (i.e the top-most managers end up reporting to each other)
+----+------------+-------+ | id | manager_id | name | +----+------------+-------+ | 1 | 2 | Matt | | 2 | 1 | Simon | | 3 | 1 | John | | 4 | 2 | Bob | | 5 | 4 | Bill | +----+------------+-------+
Given a user such as Bill (id=5)
I’m after their reporting line:
Matt > Simon > Bob > Bill
(ideally it just stops at Matt when it tries to go to a node already traversed)
Advertisement
Answer
A recursive CTE
is what you’re looking for.
Data sample
CREATE TEMPORARY TABLE t (id INT, manager_id INT, name TEXT); INSERT INTO t VALUES (1,2,'Matt'),(2,1,'Simon'), (3,1,'John'),(4,2,'Bob'), (5,4,'Bill');
Query
WITH RECURSIVE man(a,b,c) AS ( SELECT manager_id, id,name FROM t UNION SELECT man.a, id,man.c FROM man, t WHERE man.b = manager_id) SELECT a,c FROM man WHERE b = 5; a | c ---+------- 4 | Bill 2 | Bob 1 | Simon 2 | Matt (4 Zeilen)
Demo: db<>fiddle