Have the same table structure to represent a folder tree: CREATE TABLE FOLDERS([ID] INTEGER PRIMARY KEY, [PARENT_ID] INTEGER, [NAME] TEXT, [LEVEL] INTEGER, [RANK] TEXT)
Data is as follows: ID PARENT_ID NAME LEVEL RANK ------------------------------------------------------------------------------------------- 1 0 Main 0 0 2 1 Diabetic audit 1 0000000001-0000000002 3 1 GROUP BY 1 0000000001-0000000003 4 1 xxxxx 1 0000000001-0000000004 8 1 Common 1 0000000001-0000000008 9 3 zzz 2 0000000001-0000000003-0000000009 10 9 yyy 3 0000000001-0000000003-0000000009-0000000010 Now I would like the following output: In first column the ID of the folder and in the second column the full path of that folder. This will give the right ID's and the right folders in the right order by doesn't give the full path: select f.id, f.name as name from folders f where f.id in (with recursive branch(id) as (select id from folders where id = 1 union all select f.id from folders f inner join branch b on(f.parent_id = b.id)) select id from branch) order by rank This will give the right full paths in the right order but doesn't give me the right folder ID (1 for all rows): with recursive folderpath(id, parent_id, path, rank) as (select id, parent_id, name, rank from folders union all select f.id, f.parent_id, f.name || '/' || fp.path, fp.rank from folders f inner join folderpath fp on (f.id = fp.parent_id)) select id, path from folderpath where id = 1 order by rank How do I get the right folder ID and the full paths? RBS On Sun, May 12, 2019 at 1:15 PM Igor Tandetnik <i...@tandetnik.org> 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; > > -- > Igor Tandetnik > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
