Ignore this.
Was mailed over 1w ago and only came through and I have figured this out
after
studying the CTE documentation on the SQLite site.
RBS
On Wed, Feb 6, 2019 at 7:24 PM Bart Smissaert <bart.smissa...@gmail.com>
wrote:
>
> 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
