Thanks, second link regarding the extension looks interesting. RBS
On Thu, Jan 31, 2019 at 8:32 AM Peter Johnson <pe...@missinglink.co.nz> wrote: > some relevant links: > > http://dwhoman.com/blog/sql-transitive-closure.html > > > http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/ > > On Wed, 30 Jan 2019, 4:52 AM Bart Smissaert <bart.smissa...@gmail.com > wrote: > > > Working on an Android app and part of that is storing SQL in a virtual > > folder system in SQLite. For this I want to use a so-called closure table > > as explained nicely here: > > > > http://technobytz.com/closure_table_store_hierarchical_data.html > > > > I have a table holder the folder details: > > > > ID PARENT_ID Folder > > > > ------------------------------------- > > 1 0 Folder1 > > 2 1 Folder2 > > 3 1 Folder3 > > 4 1 Folder4 > > 5 2 Folder5 > > 6 2 Folder6 > > > > > > And then the closure table: > > > > PARENT_ID CHILD_ID DEPTH > > > > ----------------------------------------------- > > 1 1 0 > > 2 2 0 > > 3 3 0 > > 4 4 0 > > 5 5 0 > > 6 6 0 > > 1 2 1 > > 1 3 1 > > 1 4 1 > > 2 5 1 > > 1 5 2 > > 2 6 1 > > 1 6 2 > > > > What should the SQL be to display the folders like this: > > > > Folder PARENT_ID > > Folder1 0 > > Folder2 1 > > Folder5 2 > > Folder6 2 > > Folder3 1 > > Folder4 1 > > > > > > RBS > > _______________________________________________ > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
