Page 1 of 1

[mysql]recursive select?

Posted: Thu May 10, 2007 4:04 am
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. :)

Posted: Thu May 10, 2007 4:23 am
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:

Posted: Thu May 10, 2007 4:46 am
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. :)

Posted: Thu May 10, 2007 4:57 am
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.

Posted: Thu May 10, 2007 5:04 am
by Kieran Huggins
"Modified Preorder Tree Traversal" is the coolest thing I've seen all week 8O - thanks CG!

Posted: Thu May 10, 2007 11:27 am
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.