Yes, thanks, -- breadth first does the job nicely indeed. Not sure the closure table is needed. There are some complex tasks though, that need thinking of, eg copying one folder into an other folder. I am not familiar with recursive queries and it looks complex to me. There might arise a problem with speed if there are many folders, although for now that will be long way off. Will study the mentioned link.
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
