Thanks for that, very nice indeed. The second table is used for other purposes. I think the depth column speeds up certain queries.
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
