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

Postgresql – How to get a recursive tree for a single table element

I have a table of this type

| id | parent_id | | title |

parent_id refers to the id of the same table

I need to get a recursive tree for an element knowing only its parent.

it will be clearer what I mean in the picture

On the picture i need to get recursive parent tree for element E, (С id is known) i need get A – C – E tree without B and D and other elements, only for my element E

The nesting can be even greater, I just need to get all the parents in order without unnecessary elements.

This is needed for bread crumbs on my website

How i can do this in PostgreSQL?

enter image description here

Tags:

2

Answers


  1. 0

    Join your table on herself

    SELECT t1.title, t2.title as parent, t3.title as great_parent, ...
FROM my_table t1
JOIN my_table t2 on t1.parent_id  = t2.id 
JOIN my_table t3 on t2.parent_id  = t3.id 
...
WHERE t1.title = 'curent'

    if you don’t know how many parent you have, use LEFT JOIN and do as mutch column as needed

    thanks to Marmite Bomber
    and with a small improvement to know the kinship level :

    --drop table if exists recusive_test ;
create table recusive_test (id_parent integer, id integer, title varchar);
insert into recusive_test  (id_parent , id , title) values 
    (1, 2, 'A')
    ,(2, 3, 'B')
    ,( 2, 4, 'C')
    ,( 4, 5, 'D')
    ,( 3, 6, 'E')
    ,( 3, 7, 'F')
    ,( 6, 8, 'G')
    ,( 6, 9, 'H')
    ,( 4, 10, 'I')
    ,( 4, 11, 'J');

WITH RECURSIVE search_tree(id, id_parent, title, step) AS (
    SELECT t.id, t.id_parent, t.title ,1
    FROM recusive_test t
    where title = 'I'
  UNION ALL
    SELECT t.id, t.id_parent, t.title, st.step+1
    FROM recusive_test t, search_tree st
    WHERE t.id = st.id_parent
)
SELECT * FROM search_tree ORDER BY step DESC;
    Login or Signup to reply.
  2. 0

    Use RECURSIVE query

    with recursive rec(id,parent_id, title) as (
select id,parent_id, title from t 
where title = 'E'
union all 
select t.*
from rec
join t  on t.id = rec.parent_id
)
select * from rec

id|parent_id|title|
--+---------+-----+
 5|        3|E    |
 3|        1|C    |
 1|         |A    |
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search