• Community
  • Products
  • Jira
  • Questions
  • 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?

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?

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

3 answers

1 accepted

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
)

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

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

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Sunday in Agility

You asked for it, so we delivered: images on issues have arrived

A picture tells a thousand words. And agility boards have just released their latest feature: cover images on issues – so now your board can tell a story at first glance. Upload attachmen...

184 views 1 11
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you