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
