`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?
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'
If you're using MSSQL database - yes you should use CTE query because the nesting can have variable depth. Have you tried some of the many examples available in the forums like this one ?
And if you've tried - what prevents you from getting the data ?
http://stackoverflow.com/questions/19041814/getting-all-the-children-of-a-parent-using-mssql-query
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.