• 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 Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,950 views 12 18
Join discussion

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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot