Given the following PostgreSQL table:
items
integer id
integer parent_id
string name
unique key on [parent_id, name]
parent_id is null for all root nodes
Currently I build the sql query manually, doing a join for every path element. But is seems quite ugly to me and of course it limits the possible depth.
Example:
path: holiday,images,spain
SELECT i3.*
FROM items AS i1
, items AS i2
, items AS i3
WHERE i1.parent_id IS NULL AND i1.name = 'holiday'
AND i2.parent_id=i1.id AND i2.name = 'images'
AND i3.parent_id=i2.id AND i3.name = 'spain'
I wonder if there's a better way, probably using CTE?
You can see how my current code works and what the expected output is here:
http://sqlfiddle.com/#!1/4537c/2
This should perform very well, as it eliminates impossible paths immediately:
Assuming you provide a unique path (only 1 result).
->SQLfiddle demo