I am working over postgres in db we have below table where we have PATH
column as ltree
for storing hierarchy ids.
Table (Location)-
| ID(PK) | User |PATH (parent hierarchy) |
|--------|----------|---------|
| 1 | Parent | null |
| 2 | Child-1 | 1 |
| 3 | Child-2 | 1.2 |
| 4 | Child-3 | 1.2.3 |
| 5 | AParent | null |
| 6 | AChild-2| 5 |
| 7 | AChild-3| 5.6 |
| 8 | AChild-4| 5.6 |
tried below query -
select loc.user,STRING_TO_ARRAY(concat(loc.PATH::text,'.',loc.id),'.') as path
from location loc
WHERE LOC.NAME ilike '%child%';
Result -
| ID | User | PATH |
|--------|----------|---------|
| 2 | Child-1 |{1,2} |
| 3 | Child-2 |{1,2,3} |
| 4 | Child-3 |{1,2,3,4}|
| 6 | AChild-2|{5,6} |
| 7 | AChild-3|{5,6,7} |
| 8 | AChild-4|{5,6,8} |
Here you can see we have multiple hierarchies in the table but i want the unique hierarchies till the leaf e.g.
Hierarchy -1
A>B>C
Hierarchy -2
A>B>C>D`
expected result should be -
A>B>C>D
cause A>B>C also comes under same umbrella. so the final table expected result -
| ID | User | PATH |
|--------|----------|----------|
| 4 | Child-3 |{1,2,3,4} |
| 7 | AChild-3|{5,6,7} |
| 8 | Child-4 |{5,6,8} |
Same table managing the parent-child relationship as path showing the hierarchy of user.
Please let me know if anything else required.
CodePudding user response:
You can find leaves by examining whether a node has any descendants:
select *
from location l1
where l1.path is not null
and not exists (
select from location l2
where l2.path <@ l1.path
and l2.path <> l1.path
)
order by l1.id
Test it in db<>fiddle.
The query is more complex than it could be because your use of ltree
is somewhat unlogical. Roots should not be null
, all the hierarchy information should be included in the ltree
column, while you unnecessary mixed it with id
.