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.
>
> Cheers
> Arno
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

I would take a long hard read of this article
http://web.archive.org/web/20100105135622/http://dev.mysql.com/tech-resources/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.


I recently bought "Joe Celkos SQL for Smarties: Advanced SQL
Programming" on eBay. It covers a LOT more about Nested Sets.
(http://desc.shop.ebay.co.uk/i.html?_nkw=Celkos+SQL+Smarties&_sacat=0&_dmpt=Non_Fiction&_odkw=Celkos+SQL+Smarties&_osacat=0&_trksid=p3286.c0.m270.l1313&LH_TitleDesc=1
currently showing 2 entries) and a fourth edition on Amazon
(http://www.amazon.com/Joe-Celkos-SQL-Smarties-Fourth/dp/0123820227/ref=sr_1_1?ie=UTF8&qid=1311847652&sr=8-1)



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



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

Powered by Linux