1

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 ?

Sunbird
  • 11
  • 4

0 Answers0