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
Backwards tree parsing
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
viewtopic.php?t=25708 should help you get there, although I'm not sure on selecting down the tree only..
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%'
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
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%'