Summarising subprojects

[deggy]

Hello,

I'm customising a mantis installation.

We are managing issues using subprojects. This works great for the issue managers, but I need to give project managers a higher-level view.

Basically, in the 'summary' page, at the moment the 'By Project' lines for projects which only have sub-projects are just zero-valued. I would like to be able to make each of these lines show a sum of all open, in-progress and closed bug counts for all sub-projects of that project.

I have made plenty of changes to the PHP code to achieve my customisations but I'm a programmer, not a database expert. I have a feeling that the best way to handle this would be through a SQL query. Can anybody suggest a query that would return a count of, say, unassigned bugs for all sub-projects under a particular project that are accessible to the logged-in user?

I'll share my modifications when done.

Thanks in advance if you can help

Dan

[cas]

the project hierarchy is stored in the mantis_project_hierarchy_table.
So in case you need all the unassigned issues from all subprojects of project_id = 15, your query would look like:
SELECT a.* FROM mantis_bug_table AS a , mantis_project_hierarchy_table AS b WHERE a.project_id=b.child_id AND b.parent_id=15 AND a.handler_id=0
Of course you can add other clauses.
Hope this helps to get started

[deggy]

Thanks,

That solution returns one-level down. If you have multiple-levels then it's useful to be able to return all the sub(sub(subprojects))).

This query can be implemented with SQL 8 and above and returns a list of all sub-projects of a give project:

Code: Select all

with recursive cte(child_id, parent_id) as (
    select child_id, parent_id from mantis_project_hierarchy_table
    where parent_id = 7    
       union all 
    select mt.child_id, mt.parent_id from mantis_project_hierarchy_table mt
         inner join cte t on mt.parent_id = t.child_id
)
select * from cte;

What I need to do is integrate this query with a larger SQL query that can then return the required stats (i.e. for each of the returned child_id's, how many open bugs are there which are accessible to the logged on user).

Can that be done in a single query? I would guess there are a few more joins to be done?

[cas]

Yep, think you are better of using a few more queries in combination with some intelligent programming