Google
  Web www.spinics.net

RE: Listing parent ids

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


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



[PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Plagiarism Notes]     [Postgresql]     [Yosemite News]

Powered by Linux