I can select the rank as in the previous e-mail with this recursive query:
with recursive paths(id, folder, path) as
(select id, folder, folder from folders where parent_id is null union
select folders.id, folders.folder, paths.path || '-' ||
substr('000000000', length(folders.id)) || folders.id from folders join
paths
where folders.parent_id = paths.id)
select replace(path, 'Main', '0000000001') as path from paths order by path
Not managed yet though to use this to update the rank column in the table
Folders.
Also not sure how to avoid the replace and get the rank value 000000001
directly from the ID.
Any idea how to manage these two?
RBS
On Mon, Feb 4, 2019 at 10:41 PM Bart Smissaert <bart.smissa...@gmail.com>
wrote:
> Looking at this approach of a hierarchical system:
> https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
>
> Given a table like this:
>
> ID PARENT_ID FOLDER RANK
> ---------------------------------------------------------------
> 1 0 Main 000000001
> 2 1 CC 000000001-0000000002
> 3 1 BB 000000001-0000000003
> 4 1 AA 000000001-0000000004
> 5 2 B 000000001-0000000002-0000000005
> 6 2 A 000000001-0000000002-0000000006
>
> What SQL should I use to update the field RANK if the first row is known
> to be 0000000001, but all the
> next rows are null? I tried with a non-recursive query, but couldn't work
> it out.
>
> RBS
>
>
>
> On Thu, Jan 31, 2019 at 8:02 AM Bart Smissaert <bart.smissa...@gmail.com>
> wrote:
>
>> 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
