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
Summarising subprojects
Moderators: Developer, Contributor
Re: Summarising subprojects
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
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
Re: Summarising subprojects
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:
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?
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?
Re: Summarising subprojects
Yep, think you are better of using a few more queries in combination with some intelligent programming