Probably just the order by just do 'order by parent_id,name' to group folders together and then alphabetcal?
(was expanding it... I would also start with where parent_id=0 ) with recursive foo (id, parent_id, name) as ( select * from folders where parent_id = 0 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 parent_id,name; On Tue, Jan 29, 2019 at 10:43 AM David Raymond <david.raym...@tomtom.com> wrote: > Yup, you're right. Will have to think some more on that then. > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Tuesday, January 29, 2019 1:06 PM > To: SQLite mailing list > Subject: Re: [sqlite] Displaying hierarchical structure > > 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 > _______________________________________________ > 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
