Question 89585 in PostgreSQL Question posted in
The official documentation can be found here.

SQL to retrieve entire path from a parent child table – Postgresql

I’m oversimplifying my problem a bit.

I have a table with the following format:

id parent_id name
1 [null] id1
2 1 id2
3 2 aa3
4 3 bb4
5 2 abc
6 [null] abc1

I want to retrieve, in SQL ideally the path to root – (ie. node with parent_id = null)
For example:

getPath(5) -> "id1 / id2 / abc"
getPath(4) -> "id1 / id2 / aa3 / bb4" 
getPath(6) -> "abc1"

I’ve already build a python function that constructs the path – while loop breaking when parent_id is null and inserting the name at the beginning of the string, but I’m hoping there’s a way of getting this solved with a single DB operation, as opposed to multiple selects.

Any pointers on where to find a solution?

Many thanks!

Tags:

4

Answers


  1. 0

    Depending on what you are trying to do you can no doubt improve this. Perhaps you want the path as an array for example.

    Note that something very similar will work in most modern databases from sqlite3 to IBM db2 – consult your local documentation.

    Something like:

    ;
CREATE TABLE nodes (
  "id" int primary key,
  parent_id int,
  name text,
  -- other fields go here
  unique(parent_id, id) -- creates an index
 );
 
 
 CREATE VIEW hierarchy_view AS
 WITH RECURSIVE hierarchy AS (
  -- Handle case where no parent.
  SELECT 
    id, parent_id, id root_id, name, 
    name path,
    ARRAY[id]::int[] id_path,
    0::int depth
  FROM nodes t
  WHERE parent_id is null
  UNION ALL
  -- Handle case where there is a parent.
  -- In this case the "path" is the parent_path and name, 
  -- concatenated with ' / ' as a separator.
  SELECT 
    child.id, child.parent_id, parent.root_id, child.name, 
    parent.path || (' / ' || child.name) path,
    parent.id_path || child.id id_path,
    parent.depth + 1 depth
  FROM nodes child
  INNER JOIN hierarchy parent
    ON parent.id = child.parent_id
)
SELECT * 
FROM hierarchy h;

    Now let’s populate some values:

     insert into nodes(id, parent_id, name) 
 values
 (1, null, 'foo'),
 (2, 1, 'bar'),
 (3, 1, 'baz'),
 (4, 2, 'quux'),
 (5, 4, 'ftang')
 ;

    To query:

    
-- Get path for a node
SELECT path FROM hierarchy_view WHERE id = :id

-- Get whole tree for a node, sorted by path
SELECT * FROM hierarchy_view WHERE parent_id = :parentId
ORDER BY path ASC, id_path ASC

    You will want indexes on id and parent_id at the very least.

    Login or Signup to reply.
  2. 0

    You can do it using a recursive query with concat, by simply pass the id in the extern where clause to get the path

    WITH RECURSIVE cte(id, path) AS (
  SELECT id, t.name
  FROM mytable t
  WHERE parent_id is null
  UNION ALL
  SELECT t.id, concat(path,'/', t.name)
  FROM mytable t
  INNER JOIN cte c ON c.id = t.parent_id
)
SELECT * 
FROM cte
WHERE id = 5;

    Demo here

    Login or Signup to reply.
  3. 0

    The query below is comprised of a function containing a recursive cte to generate the path. The function can then be called per your example to produce the path elsewhere in your script:

    create or replace function getPath(node_id int) returns text as $$
   with recursive cte(id, p_id, v) as (
      select t.* from tbl t where t.parent_id is null
      union all
      select t.id, t.parent_id, c.v||'/'||t.name from cte c 
      join tbl t on  t.parent_id = c.id
   )
   select min(c.v) from cte c where c.id = node_id
$$
language sql

    Usage:

    select getPath(5) -- id1/id2/abc

    See fiddle

    Login or Signup to reply.
  4. 0

    If you are retrieving the path for each id returned in another result set, it would make more sense to join directly to a recursive CTE which is seeded with the root node of the hierarchy.

    When getting/building the path for a specific node it does not make sense to start at the root and build the entire tree. We should be starting with the specified node and walking up the tree:

    WITH RECURSIVE cte (id, parent_id, path) AS (

    SELECT id, parent_id, name
    FROM tbl
    WHERE id = 5 -- the id of the node for which we need to build the path

    UNION ALL

    SELECT t.id, t.parent_id, CONCAT_WS(' / ', t.name, path)
    FROM tbl t
    JOIN cte c ON t.id = c.parent_id

)
SELECT path
FROM cte
WHERE parent_id IS NULL;

    Or wrapped in a function:

    CREATE OR REPLACE FUNCTION getPath(node_id INT) RETURNS TEXT AS $$
    WITH RECURSIVE cte (id, parent_id, path) AS (
        SELECT id, parent_id, name
        FROM tbl
        WHERE id = node_id
        UNION ALL
        SELECT t.id, t.parent_id, CONCAT_WS(' / ', t.name, path)
        FROM tbl t
        JOIN cte c ON t.id = c.parent_id
    )
    SELECT path FROM cte WHERE parent_id IS NULL
$$
LANGUAGE SQL

    Usage:

    SELECT getPath(5); -- returns "id1 / id2 / abc"

    Here is a db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search