This looks a nice and simple way to display the tree in the right order without recursive SQL:
https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql Will do some testing on large numbers to see how the 2 methods compare speed-wise. RBS On Tue, Jan 29, 2019 at 8:33 PM Keith Medcalf <kmedc...@dessus.com> wrote: > > See https://sqlite.org/lang_with.html > > which includes how to traverse the recursive tree in either depth-first or > breadth-first order. > > Why do you need the closure table at all? > > > create table folders > ( > id integer primary key, > parent_id integer references folders, > name text not null collate nocase, > check (not (parent_id is null and id != 1)) > ); > > insert into folders values (1, null, 'Folder1'), > (2, 1, 'Folder2'), > (3, 1, 'Folder3'), > (4, 1, 'Folder4'), > (5, 2, 'Folder5'), > (6, 2, 'Folder6'); > .head on > .mode column > .width 30 9 38 > > -- depth first > > with foo (id, parent_id, name, level, path) > as (select folders.*, 0, folders.name > from folders > where parent_id is null > union all > select folders.*, level + 1, foo.path || '\' || folders.name > from foo, folders > where folders.parent_id = foo.id > order by 4 > ) > select substr(' ', 1, (level - 1) * 4) || name as > Folder, > coalesce(parent_id, 0) as PARENT_ID, > path as FullPath > from foo; > > > -- breadth first > > with foo (id, parent_id, name, level, path) > as (select folders.*, 0, folders.name > from folders > where parent_id is null > union all > select folders.*, level + 1, foo.path || '\' || folders.name > from foo, folders > where folders.parent_id = foo.id > order by 4 desc > ) > select substr(' ', 1, (level - 1) * 4) || name as > Folder, > coalesce(parent_id, 0) as PARENT_ID, > path as FullPath > from foo; > > > > SQLite version 3.27.0 2019-01-28 00:42:06 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> create table folders > ...> ( > ...> id integer primary key, > ...> parent_id integer references folders, > ...> name text not null collate nocase, > ...> check (not (parent_id is null and id != 1)) > ...> ); > sqlite> > sqlite> insert into folders values (1, null, 'Folder1'), > ...> (2, 1, 'Folder2'), > ...> (3, 1, 'Folder3'), > ...> (4, 1, 'Folder4'), > ...> (5, 2, 'Folder5'), > ...> (6, 2, 'Folder6'); > sqlite> .head on > sqlite> .mode column > sqlite> .width 30 9 38 > sqlite> > sqlite> -- depth first > sqlite> > sqlite> with foo (id, parent_id, name, level, path) > ...> as (select folders.*, 0, folders.name > ...> from folders > ...> where parent_id is null > ...> union all > ...> select folders.*, level + 1, foo.path || '\' || > folders.name > ...> from foo, folders > ...> where folders.parent_id = foo.id > ...> order by 4 > ...> ) > ...> select substr(' ', 1, (level - 1) * 4) || name > as Folder, > ...> coalesce(parent_id, 0) as PARENT_ID, > ...> path as FullPath > ...> from foo; > Folder PARENT_ID FullPath > ------------------------------ --------- > -------------------------------------- > Folder1 0 Folder1 > Folder2 1 Folder1\Folder2 > Folder3 1 Folder1\Folder3 > Folder4 1 Folder1\Folder4 > Folder5 2 Folder1\Folder2\Folder5 > Folder6 2 Folder1\Folder2\Folder6 > sqlite> > sqlite> > sqlite> -- breadth first > sqlite> > sqlite> with foo (id, parent_id, name, level, path) > ...> as (select folders.*, 0, folders.name > ...> from folders > ...> where parent_id is null > ...> union all > ...> select folders.*, level + 1, foo.path || '\' || > folders.name > ...> from foo, folders > ...> where folders.parent_id = foo.id > ...> order by 4 desc > ...> ) > ...> select substr(' ', 1, (level - 1) * 4) || name > as Folder, > ...> coalesce(parent_id, 0) as PARENT_ID, > ...> path as FullPath > ...> from foo; > Folder PARENT_ID FullPath > ------------------------------ --------- > -------------------------------------- > Folder1 0 Folder1 > Folder2 1 Folder1\Folder2 > Folder5 2 Folder1\Folder2\Folder5 > Folder6 2 Folder1\Folder2\Folder6 > Folder3 1 Folder1\Folder3 > Folder4 1 Folder1\Folder4 > sqlite> > > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > > > _______________________________________________ > 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
