Had another look at your solution and not sure now it is quite OK. The output comes out fine, but that seems to be due to the lucky fact that it just sort OK by folder. If I change the folder names then the output is not OK.
RBS On Tue, Jan 29, 2019 at 5:09 PM David Raymond <david.raym...@tomtom.com> wrote: > I may be missing something, but with recursive CTE's, why do you need the > second table? I'm assuming it's just to speed things up once the counts get > large? > > > sqlite> create table folders (id integer primary key, parent_id int > references folders, name text not null collate nocase, check (not > (parent_id is null and id != 1))); > QUERY PLAN > `--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) > > > sqlite> insert into folders values (1, null, 'Folder1'), (2, 1, > 'Folder2'), (3, 1, 'Folder3'), (4, 1, 'Folder4'), (5, 2, 'Folder5'), (6, 2, > 'Folder6'); > QUERY PLAN > |--SCAN 6 CONSTANT ROWS > `--SCAN TABLE folders > > > Gonna use backslashes here rather than spaces. So admitidly not exactly > what you were looking for. But some playing around with string functions > should be able to get there. > > > sqlite> with recursive foo (id, parent_id, name) as (select * from folders > where id = 1 union all select folders.id, folders.parent_id, foo.name || > '\' || folders.name from folders inner join foo on folders.parent_id = > foo.id) select * from foo order by name; > QUERY PLAN > |--CO-ROUTINE 2 > | |--SETUP > | | `--SEARCH TABLE folders USING INTEGER PRIMARY KEY (rowid=?) > | `--RECURSIVE STEP > | |--SCAN TABLE foo > | `--SEARCH TABLE folders USING AUTOMATIC COVERING INDEX (parent_id=?) > |--SCAN SUBQUERY 2 > `--USE TEMP B-TREE FOR ORDER BY > id|parent_id|name > 1||Folder1 > 2|1|Folder1\Folder2 > 5|2|Folder1\Folder2\Folder5 > 6|2|Folder1\Folder2\Folder6 > 3|1|Folder1\Folder3 > 4|1|Folder1\Folder4 > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Tuesday, January 29, 2019 10:52 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Displaying hierarchical structure > > Working on an Android app and part of that is storing SQL in a virtual > folder system in SQLite. For this I want to use a so-called closure table > as explained nicely here: > > http://technobytz.com/closure_table_store_hierarchical_data.html > > I have a table holder the folder details: > > ID PARENT_ID Folder > > ------------------------------------- > 1 0 Folder1 > 2 1 Folder2 > 3 1 Folder3 > 4 1 Folder4 > 5 2 Folder5 > 6 2 Folder6 > > > And then the closure table: > > PARENT_ID CHILD_ID DEPTH > > ----------------------------------------------- > 1 1 0 > 2 2 0 > 3 3 0 > 4 4 0 > 5 5 0 > 6 6 0 > 1 2 1 > 1 3 1 > 1 4 1 > 2 5 1 > 1 5 2 > 2 6 1 > 1 6 2 > > What should the SQL be to display the folders like this: > > Folder PARENT_ID > Folder1 0 > Folder2 1 > Folder5 2 > Folder6 2 > Folder3 1 > Folder4 1 > > > RBS > _______________________________________________ > 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
