CODEWITHSUNDEEP
sqlpostgresql
quambo
quambo

Reputation: 480

Hierarchic data with string paths - query for a node, get all parents and first level of their nodes

Say I got tree data:

- A        A
-  A0      A/A0
-   A0.0   A/A0/A0.0
-   A0.1   A/A0/A0.1
-  A1      A/A1
-   A1.0   A/A1/A1.0
-   A1.1   A/A1/A1.1
-  A2      A/A2

It is stored within a postgresql database "tree-data", with a column 'id' that is the path of the node like above and some helper columns like 'depth' (integer, representing the nodes depth in the tree), 'terminal' (boolean, is a leaf node and has no children).

What I'd like to achieve now is a query for 'A/A0/A0.0', that retrieves all parents and their first level of children.

Getting all parents is easy:

SELECT name, id, depth, terminal
     FROM "tree-data"
     WHERE 'A/A0/A0.0' LIKE id||'%'
     ORDER BY id;

This will return the following nodes:

A
A/A0
A/A0/A0.0

But this is what I need:

A
A/A0
A/A0/A0.0
A/A0/A0.1
A/A1
A/A2

Can you think of an easy and efficient way of achieving this? Optimizing/modifying the schema is possible, though not preferred.

Upvotes: 3

Views: 388

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

You can get the parent using regexp_replace() and then use the same logic you are using:

SELECT name, id, depth, terminal
FROM "tree-data"
WHERE 'A/A0/A0.0' LIKE regexp_replace(id, '/[^/]+$', '') || '%'
ORDER BY id;

Upvotes: 2

Related Questions