On 5/12/2019 6:19 AM, Philip Bennefall wrote:
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?
Just reverse the conditions. Something like this (not tested):
WITH RECURSIVE folderPath(id, parentId, path)
AS(SELECT id, parentFolderId, name FROM folders WHERE id=?1)
UNION ALL
SELECT f.id, f.parentFolderId, f.name || '/' || fp.path
FROM folders f join folderPath fp on (f.id = fp.parentId))
SELECT path FROM folderPath WHERE parentId is null;
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
