How to get a recursive tree for a single table element-CodePudding

I have a table of this type

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?

CodePudding user response：

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    |

CodePudding user response：

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;