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?
Only ever done this using an alternative to the "adjacency list model", the "modified preorder tree traversal algorithm". 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...
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.
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.
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.