I have a simple tree structure in a PostgreSQL database,
The tree
table structure looks like this :
| uid (oid) | parent (oid) | path (ltree) | label (string) |
| -------- | ------------ | ----------- | ------------- |
| 1 | null | top | Big Boss |
| 2 | 1 | top.2 | Mary |
| 3 | 1 | top.3 | Juan |
| 4 | 3 | top.3.4 | Alex |
| 5 | 3 | top.3.5 | Gérard |
| 6 | 5 | top.3.5.6 | Diego |
etc...
I run a search query though this table.
For example SELECT * FROM tree WHERE label ILIKE '%chris%'
;
From this, I want to build a diagram starting from the top and leading to the results. I need to get all the results' anscestors and the direct children of those ancestors.
To illustrate this, here is an example of the full tree, with what I want to extract from it.
The X
are the search results. The D
are nodes to be displayed with the results, and the °
are to be ignored and not displayed.
D
__________|______________
| | |
D D D
____|____ __|__ ____|____
| | | | | | | |
° ° ° D D D D D
__|__ __|__ __|__ __|__
| | | | | | | |
° ° D D ° ° X D
__|__
| |
D X
I think I can do that using multiple requests on top of my search request, but is it possible to do it with one single request ?