Assuming "right folder id" is short-hand for "the id of the rightmost folder"
...
with folderpath(id, rightmost_folder_id, parent_id, path, rank)
as (
select id, rightmost_folder_id, parent_id, name, rank
from folders
union all
select f.id, rightmost_folder_id, f.parent_id, f.name || '/' || fp.path,
fp.rank
from folders f
join folderpath fp
on (f.id == fp.parent_id)
)
select rightmost_folder_id, path
from folderpath
where id == 1
order by rank;
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert
>Sent: Monday, 13 May, 2019 15:22
>To: SQLite mailing list
>Subject: Re: [sqlite] CTE to Get Path In a Tree
>
>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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
