On 8/26/07, Dan Langille <[EMAIL PROTECTED]> wrote: > On 26 Aug 2007 at 13:28, demerphq wrote: > > On 8/26/07, Dan Langille <[EMAIL PROTECTED]> wrote: > > > On 26 Aug 2007 at 0:52, demerphq wrote: > > [...] > > > > Select all and only children of a single node in a single query: > > > > select C.* from tree P, tree C where P.left<= C.right and C.left<= > > P.right and P.id=? > > select C.* from tree P where P.parent_id = ?
I should have said "descendants", which using parent_id's representation will require a query per generation. Ie, if you want children and grandchildren (but not great grand children) then you will need 2 queries. N queries to get a tree of N generations. Nested set needs only a single query for this. I confused things by saying "all and only", which I said as you can reduce the number of queries at the cost of fetching all the nodes in a given tree by also storing a root id and using it to fetch the lot. > > > > Select all and only parents of a single node in a single query: > > > > select P.* from tree P, tree C where P.left<= C.right and C.left<= > > P.right and C.id=? > > select P.* from tree P where P.id = ? > > (assuming a node has only one parent). I guess here I should have said "ancestors". Your query will find only the parent, not the parent and its parent and its parent and etc. Again with nested set this becomes a single query. Aside: parent_id representation works for cyclic structures, nested set ensures its a directed graph. Anyway, according to another comment in this thread it doesn't matter, just thought id clarify what i was saying. Yves -- perl -Mre=debug -e "/just|another|perl|hacker/" ------------------------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ _______________________________________________ Bacula-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/bacula-devel
