Backwards tree parsing

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Backwards tree parsing

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

viewtopic.php?t=25708 should help you get there, although I'm not sure on selecting down the tree only..
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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%'
Post Reply