6

We have a multi level role hierarchy. I need to find all the users directly under a role and its sub-roles in a role hierarchy. How can this be achieved efficiently using SOQL or Apex or any other way ?

11

I would get the UserRole records you want first and then query the User records.

Grouping UserRole Records by Parent

// public with sharing class RoleHierarchy
static Map<Id, List<UserRole>> hierarchy
{
    get
    {
        if (hierarchy == null)
        {
            hierarchy = new Map<Id, List<UserRole>>();
            for (UserRole role : [SELECT ParentRoleId FROM UserRole])
            {
                if (!hierarchy.containsKey(role.ParentRoleId))
                    hierarchy.put(role.ParentRoleId, new List<UserRole>());
                hierarchy.get(role.ParentRoleId).add(role);
            }
        }
        return hierarchy;
    }
    private set;
}

Getting Relevant UserRole Records

// public with sharing class RoleHierarchy
public static List<UserRole> getChildren(Id userRoleId)
{
    return hierarchy.containsKey(userRoleId) ?
        hierarchy.get(userRoleId) : new List<UserRole>();
}

// I would just use one of the below and name it getSubHierarchy
// Not entirely clear based on OP if top-level should be included

public static Set<Id> getSubHierarchyInclusive(Id userRoleId)
{
    Set<Id> roleIds = new Set<Id> { userRoleId };
    for (UserRole childRole : getChildren(userRoleId))
        roleIds.addAll(getSubHierarchyInclusive(childRole.Id);
    return roleIds;
}
public static Set<Id> getSubHierarchyExclusive(Id userRoleId)
{
    Set<Id> roleIds = new Set<Id>();
    for (UserRole childRole : getChildren(userRoleId))
    {
        roleIds.add(childRole.Id);
        roleIds.addAll(getSubHierarchyExclusive(childRole.Id);
    }
    return roleIds;
}

Get Users

// public with sharing class RoleHierarchy
public static List<User> getUsersUnder(Id userRoleId)
{
    return [
        SELECT Id FROM User
        WHERE UserRoleId IN :getSubHierarchy(userRoleId)
    ];
}

The above approach consumes a total of 2 SOQL statements.

1

Here is an updated code of Adrian Larson Added :

  • IsActive as criteria ( because assigning a Task to a inactive user fails for example )
  • missing brackets/parenthesis :)

    // public with sharing class RoleHierarchy
    global static Map<Id, List<UserRole>> hierarchy
    {
        get
        {
            if (hierarchy == null)
            {
                hierarchy = new Map<Id, List<UserRole>>();
                for (UserRole role : [SELECT ParentRoleId FROM UserRole])
                {
                    if (!hierarchy.containsKey(role.ParentRoleId))
                        hierarchy.put(role.ParentRoleId, new List<UserRole>());
                    hierarchy.get(role.ParentRoleId).add(role);
                }
            }
            return hierarchy;
        }
    private set;
    

    }

    // public with sharing class RoleHierarchy
    global static List<UserRole> getChildren(Id userRoleId)
    {
        return hierarchy.containsKey(userRoleId) ?
            hierarchy.get(userRoleId) : new List<UserRole>();
    }
    
    // I would just use one of the below and name it getSubHierarchy
    // Not entirely clear based on OP if top-level should be included
    global static Set<Id> getSubHierarchyInclusive(Id userRoleId)
    {
        Set<Id> roleIds = new Set<Id> { userRoleId };
            for (UserRole childRole : getChildren(userRoleId))
            roleIds.addAll(getSubHierarchyInclusive(childRole.Id));
        return roleIds;
    }
    
    global static List<User> getUsersUnder(Id userRoleId)
    {
        return [
            SELECT Id FROM User
            WHERE UserRoleId IN :getSubHierarchyInclusive(userRoleId) AND IsActive=true
        ];
    }
    
1

You just need to pass a Set of Userrole ids and to code below will return users corresponding to each Userrole id after collecting all users under each subordinates.

public class AllUserRoleQuery {

    public  static Map<Id,List<Id>> GetRoleVsUSerMap(Set<Id> RoleIds){
        Set<Id> RecievedRoleIds = new Set<Id>();
        Set<Id> RolesWithoutParentIds = new Set<Id>();  
        Map<Id, Id> RoleVsParentId = new Map<Id, Id>();
        Map<Id, Set<Id>> RoleIdVsParentSeries = new Map<Id, Set<Id>>();
        Map<Id, List<Id>> RoleIdVsUSerList = new Map<Id, List<Id>>();

        List<UserRole> AllUserRoleList = new List<UserRole>();
         if(RoleIds != NULL && RoleIds.size() > 0){
            RecievedRoleIds.addAll(RoleIds);

            List<String> Userrolefield = new List<String>{'id','parentroleid'};
            if(UtilCheckCRUD.isAccessible('UserRole',Userrolefield)){

                AllUserRoleList = [Select Id, ParentRoleId From UserRole Limit 10000];

                for(UserRole usrRoleForParent : AllUserRoleList){
                    if(RoleIds.contains(usrRoleForParent.Id)){
                        if(usrRoleForParent.ParentRoleId != NULL){
                            RoleVsParentId.put(usrRoleForParent.Id, usrRoleForParent.ParentRoleId);
                        }
                    }
                }
                Boolean IterateOnceMore = true;

                while(IterateOnceMore){
                    Boolean NewRoleAdded = false;
                    for(UserRole usrRole : AllUserRoleList){
                        if(usrRole.ParentRoleId != NULL && RoleIds.contains(usrRole.ParentRoleId) && !RoleIds.contains(usrRole.Id)){
                            RoleVsParentId.put(usrRole.Id, usrRole.ParentRoleId);
                            RoleIds.add(usrRole.id);
                            NewRoleAdded = true;
                        }
                    }
                    if(!NewRoleAdded){
                        IterateOnceMore = false;
                    }
                }
            }
            //Role Id Vs ParentSeries
            for(Id roleId : RoleIds){
                if(!RoleIdVsParentSeries.containsKey(roleId)){
                    Set<Id> setInitialozation = new Set<Id>();
                    RoleIdVsParentSeries.put(roleId, setInitialozation);
                }
                Set<Id> parentSeriesSet = new Set<Id>();
                parentSeriesSet = RoleIdVsParentSeries.get(roleId);
                Id parentId = getParentId(roleId, RoleVsParentId);
                Id TempRoleId = roleId;
                while (parentId != NULL){
                    parentId = getParentId(TempRoleId, RoleVsParentId);
                    if(parentId != NULL){
                        parentSeriesSet.add(parentId);
                        TempRoleId = parentId;
                    }
                }
                if(parentSeriesSet != NULL && parentSeriesSet.size() > 0){
                    RoleIdVsParentSeries.put(roleId, parentSeriesSet);
                }
            }

            if(RoleIds.size() > 0){
                List<User> userList = new List<User>();

                List<String> UserFieldsvalue = new List<String>{'id','userroleid','isactive'};
                if(UtilCheckCRUD.isAccessible('User',UserFieldsvalue)){

                    userList = [Select Id, UserRoleId From User Where UserRoleId In : RoleIds AND IsActive = true AND Name !='Chatter Expert'];
                    if(userList.size() > 0){
                        for(User usr : userList){
                            if(usr.UserRoleId != NULL && RecievedRoleIds.contains(usr.UserRoleId)){
                                if(!RoleIdVsUSerList.containsKey(usr.UserRoleId)){
                                    List<Id> UserListinitialization = new List<Id>();
                                    RoleIdVsUSerList.put(usr.UserRoleId, UserListinitialization);
                                }
                                RoleIdVsUSerList.get(usr.UserRoleId).add(usr.id);
                            }
                            if(usr.UserRoleId != NULL && RoleIdVsParentSeries.containsKey(usr.UserRoleId)){
                                Set<Id> ParentsRoleIds = new Set<Id>();
                                ParentsRoleIds = RoleIdVsParentSeries.get(usr.UserRoleId);
                                if(ParentsRoleIds != NULL && ParentsRoleIds.size() > 0){
                                    for(Id RoleId : ParentsRoleIds){
                                        if(RecievedRoleIds.contains(RoleId)){
                                            if(!RoleIdVsUSerList.containsKey(RoleId)){
                                                List<Id> UserListinitialization = new List<Id>();
                                                RoleIdVsUSerList.put(RoleId, UserListinitialization);
                                            }
                                            RoleIdVsUSerList.get(RoleId).add(usr.id);
                                        }
                                    }
                                }
                            }

                        }
                        return RoleIdVsUSerList;
                    }
                }
            }
        }
        return RoleIdVsUSerList;
    }

    public static Id getParentId(Id RoleId, Map<Id,Id> RoleVsParentId){
        if(RoleId != NULL && RoleVsParentId != NULL && RoleVsParentId.size() > 0){
            if(RoleVsParentId.containsKey(RoleId) && RoleVsParentId.get(RoleId) != NULL){
                return RoleVsParentId.get(RoleId);
            }
        }
        return null;
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.