Hi everyone,
I have a tree of folders and I want to find the complete path from any
arbitrary point back to the top level directory. The schema is:
CREATE TABLE IF NOT EXISTS folders(
id INTEGER PRIMARY KEY,
parentFolderId INTEGER REFERENCES folders(id) ON DELETE CASCADE ON
UPDATE CASCADE,
name TEXT NOT NULL);
I made the following CTE:
WITH RECURSIVE folderTree (id, path)
AS(SELECT id, name FROM folders WHERE parentFolderId IS NULL
UNION ALL
SELECT folders.id, folderTree.path || '/' || folders.name
FROM folders, folderTree WHERE folders.parentFolderId = folderTree.id)
SELECT path FROM folderTree WHERE id=?1;
This produces the correct result, but I am wondering if there is a more
efficient way? This query seems to generate the entire tree and then do
a table scan to find just the one row I am looking for. Can I start from
the given row and *only* traverse upwards through the levels until I
find a node with no parent?
Thanks in advance for any pointers.
Kind regards,
Philip Bennefall
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
