[SOLVED] Select all rows that have ID in materialized path

Select all rows that have ID in materialized path

I have this a tree structured table with a materialized path column (matpath). The data looks like this:

ID    MATPATH    PARENT
---------------------
1     NULL       NULL
2     1.         1
3     1.2.       2
4     1.2.3.     3
5     1.2.       2
6     1.2.3.4.   4
7     1.2.5.     5

etc

Given the ID, how can I get all elements that are above (one query) or below (anther query)? For example, if the ID is 7, I want to select rows with IDs 1, 2 and 5 in addition to 7. If the given ID is 3, select 1, 2 and 3. And so on.

Thank you.

Solution

First you have to decide if you want the trailing . on your materialized paths, I'll assume that you do want them because it will make life easier.

Something like this will get you the nodes below:

select id
from tree
where matpath like (
    select matpath || id || '.%'
    from tree
    where id = X
)

Where X is the node you're interested in. Your tree looks like this:

1 --- 2 -+- 3 --- 4 --- 6
         |
         +- 5 --- 7

And applying the above query with a few values matches the diagram:

X | output
--+--------------
3 | 4, 6
7 |
2 | 3, 4, 5, 6, 7

Getting the nodes above a given node is easier in the client: just grab the matpath, chop off the trailing ., and then split what's left on .. SQLite's string processing support is rather limited, I can't think of a way to split the materialized path without trying to add a user-defined split function (and I'm not sure that the appropriate split could be added).

So two queries and a little bit of string wrangling outside the database will get you what you want.