Page 1 of 1

Backwards tree parsing

Posted: Fri Aug 26, 2005 11:44 pm
by jwalsh
Hi,

We have a tree structure data table, for storing heirarchical data...

Parent
...Child
...Child
......Child of Child
...Child
..........Child of Child of Child

with an infinite possibility of children. Now, I'd like to using SQL enter a SELECT a parent ID and have all it's children (and childrens children...etc) all returned as well.

I know I can use SELECT in SELECT to do one tier, but how can I have it recursively check under that. I seem to recall this functionality somewhere being told to me.

I'm confident I could do this using a PHP recursive loop, but using SQL seems so much more elegant... plus I'm just curious how to do it anyway.

Before you warn me: I'm well aware of the system load doing this with lots of children, however, this is a minimal database, without too much data in it at all. Plus, we wouldn't be selecting parent's way down towards generation 1 of the tree anyway. Probably returning between 10-30 rows working properly.

Thanks as always,

Josh

Posted: Fri Aug 26, 2005 11:56 pm
by feyd
viewtopic.php?t=25708 should help you get there, although I'm not sure on selecting down the tree only..

Posted: Sat Aug 27, 2005 12:01 am
by jwalsh
Yeah, I've seen that already... Used it many times, it's the backwardsness that's confusing. We've found an alternate solution for now, but I'd still be curious to know if someone knows how to do recursive queries in SQL.

Posted: Sat Aug 27, 2005 5:07 am
by timvw
You can't recurse with sql.. (Well, meaby you can with stored procedures).
But you'll probably want to use php to do the recursion.. And then perform simple SQL queries..

But as i mentionned in that thread already, it's more efficient if you can avoid recursion (and thus avoid an explosion of queries). Back in those days i couldn't find the link, but i did now :) http://www.dbazine.com/oracle/or-articles/tropashko4 explain a couple of possible solutions.

For example, if you use "materialzed path" you could have a parent with id '2.1'.
If you want all the subnodes, you simply SELECT * FROM table WHERE parent_id LIKE '2.1%'