CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expressionrecursive-query
BARNZ
BARNZ

Reputation: 1329

Postgres recursive query - get path to root given a leaf node

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)

Upvotes: 3

Views: 2075

Answers (1)

Jim Jones
Jim Jones

Reputation: 19623

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

Upvotes: 7

Related Questions