Thanks that will be helpful, I liked the idea of paths also. However if you have 200,000 + nodes in a tree the paths might be coming hard to work with.
On Wed, Apr 11, 2012 at 11:58 AM, villas <villa...@gmail.com> wrote: > I've just been working with a tree myself. Database recursion (CTE) seems > very effective and is now supported by most of the larger DBs (although > sadly not Sqlite yet, I don't think). > This is the reference link for Postgres and a SQL query I wrote for > Firebird, > I thought it might vaguely help if you are heading in that direction: > > > http://old.storytotell.org/blog/2009/08/11/postgresql84-recursive-queries.html > sql = """ > WITH RECURSIVE breadcrumb(id, descr, parent_id) AS ( > SELECT id, descr, parent_id FROM area a WHERE id = %s > UNION ALL > SELECT a.id, a.descr, a.parent_id FROM area a, breadcrumb > WHERE breadcrumb.parent_id = a.id) > SELECT * FROM breadcrumb; > """ % id > rows = db.executesql(sql,as_dict=True) > > However, I also like the method of saving paths in a field (I believe the > method is called "Materialized Path"). It seems simple and versatile and I > already decided to try it next time I need a simple tree and of course this > would work fine on Sqlite too. > > Best wishes, > David > > > > > On Wednesday, 11 April 2012 15:41:05 UTC+1, Detectedstealth wrote: >> >> Yeah probably is I already have it with web2py but it is slow with 20,000 >> nodes takes around 5-10 seconds to load the results. >> >> On Wed, Apr 11, 2012 at 7:29 AM, Richard Vézina < >> ml.richard.vez...@gmail.com> wrote: >> >>> I made a lot of that last year... Sometimes it was driving me nuts, >>> needing to have the same columns for each table in the union... Good naming >>> convention helps to make thing clearer when you get back to the code... >>> >>> Note, I would try to make it with web2py first if I were needing to >>> write those code again. >>> >>> Richard >>> >>> >>> On Wed, Apr 11, 2012 at 10:09 AM, Bruce Wade <bruce.w...@gmail.com>wrote: >>> >>>> Yeah not sure have never used recursion at the database level. However >>>> it seems to be the only option, none of the other options in that chapter >>>> fit my needs. >>>> >>>> >>>> On Wed, Apr 11, 2012 at 7:05 AM, Richard Vézina < >>>> ml.richard.vez...@gmail.com> wrote: >>>> >>>>> Recursion is slow because the union... But it may fit yours need >>>>> better I don't know. >>>>> >>>>> Richard >>>>> >>>>> >>>>> On Wed, Apr 11, 2012 at 9:45 AM, Bruce Wade <bruce.w...@gmail.com>wrote: >>>>> >>>>>> Ok so I have read the chapter I think the best option is postgres >>>>>> recursive queries. >>>>>> >>>>>> On Tue, Apr 10, 2012 at 10:39 AM, Richard Vézina < >>>>>> ml.richard.vez...@gmail.com> wrote: >>>>>> >>>>>>> Yes AntiPattern cover 4 or 5 kind of tree representation, classify >>>>>>> them depending of usage and gives pros and cons. >>>>>>> >>>>>>> I choose Closure table since it was one of the more complet for my >>>>>>> case. But if parent node is changing frequently it's not the more >>>>>>> effecient >>>>>>> tree antipattern. >>>>>>> >>>>>>> Have look it is pretty instructive. >>>>>>> >>>>>>> Book contain little more explanation, but there is this slide : >>>>>>> >>>>>>> http://www.slideshare.net/**billkarwin/practical-object-** >>>>>>> oriented-models-in-sql<http://www.slideshare.net/billkarwin/practical-object-oriented-models-in-sql> >>>>>>> >>>>>>> Richard >>>>>>> >>>>>>> >>>>>>> On Tue, Apr 10, 2012 at 7:32 AM, stefaan <stefaan.hi...@gmail.com>wrote: >>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> Well I know the major bottle neck in the site is the binary tree. I >>>>>>>>> am still trying to figure out how to do this the best and most >>>>>>>>> efficient. >>>>>>>>> >>>>>>>> >>>>>>>> Maybe this could be useful: http://dirtsimple.org/** >>>>>>>> 2010/11/simplest-way-to-do-**tree-based-queries.html<http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> -- >>>>>> Regards, >>>>>> Bruce Wade >>>>>> http://ca.linkedin.com/in/**brucelwade<http://ca.linkedin.com/in/brucelwade> >>>>>> http://www.wadecybertech.com >>>>>> http://www.fittraineronline.**com <http://www.fittraineronline.com>- >>>>>> Fitness Personal Trainers Online >>>>>> http://www.warplydesigned.com >>>>>> >>>>>> >>>>> >>>> >>>> >>>> -- >>>> -- >>>> Regards, >>>> Bruce Wade >>>> http://ca.linkedin.com/in/**brucelwade<http://ca.linkedin.com/in/brucelwade> >>>> http://www.wadecybertech.com >>>> http://www.fittraineronline.**com <http://www.fittraineronline.com> - >>>> Fitness Personal Trainers Online >>>> http://www.warplydesigned.com >>>> >>>> >>> >> >> >> -- >> -- >> Regards, >> Bruce Wade >> http://ca.linkedin.com/in/**brucelwade<http://ca.linkedin.com/in/brucelwade> >> http://www.wadecybertech.com >> http://www.fittraineronline.**com <http://www.fittraineronline.com> - >> Fitness Personal Trainers Online >> http://www.warplydesigned.com >> >> -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.fittraineronline.com - Fitness Personal Trainers Online http://www.warplydesigned.com
