[sqlite] recursive select in sqlite
dear Sqlite group, i wonder is there RECURSIVE select function in sqlite? the background for the question are: create table objects (id INTEGER PRIMARY KEY AUTOINCREMENT, name text unique) create table tree(id int, child_id int, PRIMARY KEY(id, child_id)) i want to draw the whole tree, is there good solution for the function? thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] recursive select in sqlite
Hello z i wonder is there RECURSIVE select function in sqlite? the background z for the question are: create table objects (id INTEGER PRIMARY KEY z AUTOINCREMENT, name text unique) create table tree(id int, child_id z int, PRIMARY KEY(id, child_id)) i want to draw the whole tree, is there z good solution for the function? If you want to store a tree structure in relational tables, then you could read up on things like this: http://articles.sitepoint.com/article/hierarchical-data-database The model on the second page is the one to go for. Hope this helps. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] recursive select in sqlite
On Mon, May 17, 2010 at 09:40:32PM +0800, zeal scratched on the wall: dear Sqlite group, i wonder is there RECURSIVE select function in sqlite? the background for the question are: create table objects (id INTEGER PRIMARY KEY AUTOINCREMENT, name text unique) create table tree(id int, child_id int, PRIMARY KEY(id, child_id)) i want to draw the whole tree, is there good solution for the function? thanks. No, recursive selects are not supported. If you use an adjacency model (like your example) you need to have your application loop over the tree (for arbitrary sized trees; if you know the depth of the tree you can use a series of joins). If you use a nested set model, you can do more with single queries, but the insert/update/delete costs are much higher. See any good SQL book and/or web tutorials for more info. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] recursive select in sqlite
z i wonder is there RECURSIVE select function in sqlite? the background z for the question are: create table objects (id INTEGER PRIMARY KEY z AUTOINCREMENT, name text unique) create table tree(id int, child_id z int, PRIMARY KEY(id, child_id)) i want to draw the whole tree, is there z good solution for the function? If you want to store a tree structure in relational tables, then you could read up on things like this: http://articles.sitepoint.com/article/hierarchical-data-database The model on the second page is the one to go for. Just adding a note to this good advice: depending on your application current and future needs, you may find it useful to store a level column, with for instance, 0 being the root, 1 the first level branches or leaves, aso. If ever you have usage for it, it will simplify many queries a great lot while eating only little space. As an example, you can then build a hierarchical SQL view of the tree or subtree, displaying it exactly as the display_tree() PHP function shown on this page, all with simple SQL. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
