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 >>>>>> >>>>>> 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 >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> -- >>>>> 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 >>>>> >>>>> >>>> >>> >>> >>> -- >>> -- >>> 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 >>> >>> >> > > > -- > -- > 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 > >
