How to get all users within a group (recursively expanding sub-groups) with SQL

Daniel Khodabak

`projectroleactor` contains a role mapping for different users and groups for specific projects. It's easy enough to get users assigned a role by querying `where projectroleactor.ROLETYPE = 'atlassian-user-role-actor'`, but when it comes to getting all the users within the groups assigned with `atlassian-group-role-actor`, I'm finding it difficult to drill down into the subgroups.

What is the best way to recursively query groups and sub-groups that may be 2. 3. or more levels deep? Would this include using a Recursive CTE type of query?

Daniel Khodabak

I created the following query to get all users with the role affecting a particular project pkey:

with group_cte (parent_id, child_id)
as (
	select
		cast(0 as decimal),
		g.id
	from projectroleactor pra
	join project p on pra.pid = p.id
	join cwd_group g on pra.ROLETYPEPARAMETER = g.group_name
	where p.pkey = 'YOUR_PROJECT_PKEY'
	and pra.ROLETYPE = 'atlassian-group-role-actor'
	union all
	select
		cast(m.parent_id as decimal),
		m.child_id
	from cwd_membership m
	join group_cte cte on m.parent_id = cte.child_id
	where m.membership_type = 'GROUP_GROUP'
)
select
	u.display_name,
	u.user_name
from cwd_user u
join cwd_membership m on u.id = m.child_id
join group_cte cte on m.parent_id = cte.child_id
where m.membership_type = 'GROUP_USER'
union
select
	u.display_name,
	u.user_name
from cwd_user u
join projectroleactor pra on u.user_name = pra.ROLETYPEPARAMETER
join project p on pra.pid = p.id
where p.pkey = 'YOUR_PROJECT_PKEY'
and pra.ROLETYPE = 'atlassian-user-role-actor'