On 5/12/2019 2:14 PM, Igor Tandetnik wrote:
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;
Thanks, that seems to work with a couple of very minor tweaks. The query
plans are somewhat different and my gut feeling is that the one you
wrote is better, but I will measure against some larger datasets just to
be sure.
Thanks for the quick response!
Kind regards,
Philip Bennefall
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
