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
