Method or Query

I need functionality that will return all project id’s related to a given project Id. The problem is it can’t just return id’s with the given project ID as the parent ID. It will also need to return all of their sub units, and their sub units and so on and so forth.I’ve been looking at how I can do this and as far as I can see this wouldn’t be achievable using a standard query created in the AOT.

I wondered if it was possible to create through coding a query or will I just need to setup a method that finds and returns this value through select statements?

With the current data model, you have to use a method and recursively traverse the whole subtree.

If the performance was not sufficient, you would have to redesign the data model and maintain some additional information for more efficient queries. Several options are available, for example you can implement Nested set model: each parent node will store children in some range, the query would ask for that range and returns all project in the subtree in one simple indexed query.

Thanks. I’ve come to a similar conclusion. Since this functionality is only going to be used to display all sub units of a root project with no parent id I’m just going to add a field storing the root projid of that particular structure. On one hand its a bit of a redunent field but on the other hand it saves on the overhead of having to run multiple selects to find the all the different sub units, and their sub units and so on.