On 28 July 2011 12:30, Arno Kuhl <arno@xxxxxxxxxxxxxx> wrote: > On 28 July 2011 10:39, Arno Kuhl <arno@xxxxxxxxxxxxxx> wrote: >> Arno Kuhl wrote: >>> Not strictly a php issue but it's for a php app, hope that counts >>> (plus I haven't had much joy googling this) >>> >>> I have a table with an id and a parentid. >>> If it's a top-level record the parentid is 0, otherwise it points to >>> another record, and if that record isn't a top-level record its >>> parentid points to another record, etc (a linked list). >>> >>> Is there a single select that will return the complete list of parentids? >>> Or do I have to iterate selecting each parent record while parentid> >>> 0 and build the list entry by entry? >> >> Little difficult to answer what you don't say what you are using as a >> database. >> Recursive queries are now possible on many databases, except I think >> for MySQL. >> I run this type of query all the time on Firebird and Postgres now >> supports the same CTE functions. >> >> Lester Caine - G8HFL >> ----------------------------- >> >> I'm currently using MySQL but I'll switch databases if there's a >> compelling reason and no drawbacks. >> Thanks for the lead, I'm googling recursive queries. >> >> Arno >> -- > > I would take a long hard read of this article > http://web.archive.org/web/20100105135622/http://dev.mysql.com/tech-resource > s/articles/hierarchical-data.html > > I can't find it anywhere else now - it used to be on the mysql site - but > gone since Oracle has it and I can't find it in Google Cache. > > But, it explains the pros and cons of using the Adjacency List Model vs the > Nested Set Model. > > The article is quite old (the copyright on the page is 2008, but I've no > idea when it was actually created) and so, there are advances in SQL > features (CTE's being one of them) which aren't mentioned. > > But, I've found Nested Sets to be much easier for me to work with, allowing > me to provide quite complex searching based upon an n-level tree. > > How you visualise the data won't change. It is still, visually at least, a > set of parent/child relationships, but to build a tree, you don't need to > use recursion. In most cases, a single query will be enough to interact with > the tree at any level, in any direction, for more or less any purpose. > > Richard Quadling > -- > > Thanks Richard. Your reference is exactly what I was looking for. > I'm just busy reading a sitepoint article about adjacent lists vs the > niftily titled "modified preorder tree traversal model". > http://www.sitepoint.com/hierarchical-data-database/ (really old - 2003) > I found I'm using the adjacent list model at the moment (didn't know it had > a name, I always thought of it as a type of linked list). > The "modified preorder tree traversal model" in the sitepoint article > appears to be equivalent to the "nested set model" in the mysql article. > It seems simple enough to implement, I'll definitely give it a closer look > and do some tests. > > Thanks, it's great to hear the experiences of others who've used this. > > Cheers > Arno > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > In my day job, I process a LOT of data (MS SQL Server, 15 or so DBs, maybe 250GB of data, at least 15 years of trends,etc.). So I have a LOT of trees. Customer hierarchies (Customer Head Office, Regional Office, Branch), Location hierarchies (Continent, Country, Region, City, Street), Product hierarchies. BOM, etc. When it comes to analysis, I can ask questions like "Which European Customers have, overall, increased their turnover by at least 20% in the last 6 months for a single product type?" Because of the nested sets, I know that "European" means a left/right of 23 to 224. Any customer branch with a location id in that range is eligible for inclusion. No recursion of finding the European ID and then chugging through all the IDs down to the street level to match that ID to the customer. And then realising that not all the customers are tagged at street level, but maybe just at the city level. It allows faster grouping and drilldown in my mind as the data is always filtered for the required set in question. And if you are filtering over multiple sets (location, date, product category), you are going to get to the results a LOT faster than with the easier understood, but not as useful (IMHO) adjacent list. Richard. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
