How do I query the JIRA DB to get the list of projects a user has a role on, based on individual and group membership?

[Gaël NEUEZ]

The following query allows me to search all projects on which a user has the "Users" role. But it does only include 'individual' rights. If the user belongs to a group that is in the "Users" role of a project, the project is not returned. Can anyone help me to include this part as well?

Thx

SELECT DISTINCT (

P.pname

)

FROM project P

JOIN projectroleactor PRA ON P.id = PRA.pid

WHERE PRA.roletypeparameter = {currentuser}

AND PRA.projectroleid =10000

order by P.pname asc

[Gaël NEUEZ · Accepted solution]

Solved the MySQL query duration issue by indicating the first select is a subquery:

SELECT DISTINCT `PID`
FROM `projectroleactor`
WHERE `PROJECTROLEID` =10000
AND `ROLETYPE` LIKE 'atlassian-group-role-actor'
AND `ROLETYPEPARAMETER` IN
(
select * from (SELECT DISTINCT `parent_name` FROM `cwd_membership` WHERE `child_name` LIKE 'username') as subquery
)

[Gaël NEUEZ]

Thanks Tiago, I digged myself into the database scheme and found a similar solution:

SELECT DISTINCT `PID`
FROM `projectroleactor`
WHERE `PROJECTROLEID` =10000
AND `ROLETYPE` LIKE 'atlassian-group-role-actor'
AND `ROLETYPEPARAMETER` IN
(
SELECT DISTINCT `parent_name` FROM `cwd_membership` WHERE `child_name` LIKE 'username'
)

My problem now is that in my MySQL console, the query never returns... It hangs forever, looks like it does not like the nested 'select' statement in the 'where' clause... Just need to find out why...

=>

That's MySQL handling poorly nested select with performance issue. Would need to transform that if possible with a 'join' statement instead.

Regards

Gael

[Tiago Comasseto]

Hi Gael,

This query should return the same thing as your original query, but will include all members of the group JIRA-users:

SELECT DISTINCT (P.pname)
FROM project P
JOIN projectroleactor PRA ON P.id = PRA.pid
WHERE PRA.roletypeparameter in (select child_name from cwd_membership where parent_name = 'jira-users')
AND PRA.projectroleid =10000
order by P.pname asc

I hope it helps.

Cheers