CODEWITHSUNDEEP
sqlpostgresqlhierarchical-data
SomethingElse
SomethingElse

Reputation: 327

Get tree in PostgreSQL

I have a data many to many relationship. E.g one main parent can have multiple children and those children can have multiple parents.

My main table is 

| id | name            | depth  |
---------------------------------
 1   |  top parent     |    0
 2   |  child1 of 1    |    1
 3   |  child2 of 1    |    1
 4   |  child1 of 2    |    2
 5   |  child2 of 2/3  |    2
 6   |  child1 of 5    |    3

And linked table looks like 

| childId | parentId |
----------------------
    2     |    1
    3     |    1 
    4     |    2
    5     |    2
    5     |    3 
    6     |    5

Now I want to get all direct parents, siblings and direct children of given entry. For example for id 5 I want to get 2,3, 4 and 6. I am new at PostgreSql. How can I do it in one query? I need to order the result by name and paginate.

Upvotes: 0

Views: 483

Answers (2)

light souls
light souls

Reputation: 728

This gives you every combination (parents/siblings/children).

select m.id, m.name,string_agg(distinct t.ids::text,',')
from main_table m
join linked_table l on l.childid = m.id or l.parentid = m.id 
join linked_table l3 USING(parentid),
unnest(ARRAY[l.parentid,l.childid,l3.childid]) as t(ids)
where t.ids <> m.id
group by m.id, m.name
order by m.name;

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246318

You don't need recursive queries for that.

Get the direct parents:

SELECT parent_id
FROM link
WHERE child_id = 5;
┌───────────┐
│ parent_id │
├───────────┤
│         2 │
│         3 │
└───────────┘
(2 rows)

Get the siblings:

SELECT b.child_id AS sibling_id
FROM link a
   JOIN link b USING (parent_id)
WHERE a.child_id = 5
   AND b.child_id <> 5;
┌────────────┐
│ sibling_id │
├────────────┤
│          4 │
└────────────┘
(1 row)

Get the children:

SELECT child_id
FROM link
WHERE parent_id = 5;
┌──────────┐
│ child_id │
├──────────┤
│        6 │
└──────────┘
(1 row)

Upvotes: 2

Related Questions