[mysql]recursive select?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

[mysql]recursive select?

Post by Jenk »

I have been asked to ensure that for every project we have in our Mantis records, that when the parent project has a new version added, this version is also added to every sub-project. Parent projects can have any number of sub-projects, and those sub-project too can have sub-projects (and etc. into oblivion). I've done this before, but using php and multiple queries, but this time I was wondering if it is possible to recursively select?

Table structure of the relevant table is simple:

Code: Select all

mysql> desc mantis_project_hierarchy_table;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| child_id  | int(10) unsigned | NO   |     |         |       | 
| parent_id | int(10) unsigned | NO   |     |         |       | 
+-----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
so as you can see, I would need to loop down collecting the ids.. any suggestions?

Much obliged. :)
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Only ever done this using an alternative to the "adjacency list model", the "modified preorder tree traversal algorithm". 8O
Storing Hierarchical Data in a Database Using this method it is possible to get complete trees etc with one select.

For one of main database tree's we actually have both methods are in place. The adjacency/recursion is legacy and code hasn't changed to use it but we also take advantage of the preorder algorithm where possible. OK has impact of maintaining the preorder information in legacy code but we created code to simply refresh the tree at the users request (also whenever someone created/moved a node using new functionality). Not ideal I would have prefered to change all the code to be correct but... :cry:
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Unfortunately that wouldn't work for me, I don't know what the lft/rgt values would be without recursively searching in the first place; and all projects are created dynamically :)

Besides which, the table is from a 3rd party app so modifying it is not ideal. Recursive function it will have to be I think, should be ok.. it's an internal app and no more than 5 users anyway, so performance shan't be an issue :) but thank you none the less for the info. :)
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Had a funny feeling that would be the case. Sorry it didn't help but changing the model is the only way I know to do what you wanted. Anyway the link is there if anyone else needs it.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

"Modified Preorder Tree Traversal" is the coolest thing I've seen all week 8O - thanks CG!
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

I don't think a recursive select is possible with MySQL. You are going to have to select all the records you want to work with into an array, then recurse through it. This is the only method I know of besides switching to preorder as mentioned.
Post Reply