function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sudhirn@merunetworks.comsudhirn@merunetworks.com 

How to get role hierarchy in SOQL query

Hi, 

  I need to get all the user role and sub roles within sub role that are associated to a parent role using soql query. Below is the code which i am using to get role and sub-roles data. 

 /* Here I pass user name get the userrole id */
 user gurs = [Select UserRoleId,Email,ForecastEnabled 
                     from user where id = :runningUserId LIMIT 1];

/*  Here I pass the userrole id to see parentroleid */
 List<User> UsrList = [Select FirstName, Id, LastName, UserRoleId, UserRole.Name 
                            from User 
                            Where UserRole.ParentRoleId = :gurs.UserRoleId];

I am able to see below roles when i run the above soql code. 

 Example : 
     EMEA - Level - 1
          EMEA- Sub Level -2 

My new requirement is 

 Example 
     EMEA - Level - 1
          EMEA- Sub Level -2 
              EMEA - Super Sub Level-3

If I pass user name I should be able to see all the 3 level hierarchy data if there are ever 4 I should be able to see all the 4 level 

Please suggest me apex experts how i can achive this requirement need you suggestion 

Thanks
Sudhir
Best Answer chosen by sudhirn@merunetworks.com
Mahesh DMahesh D
Hi Sudhir,

Please copy and paste the below code by going to Setup --> Apex Classes --> New
  
public class Utility {
    // To get all sub roles.
    public static Set<ID> getAllSubRoleIds(Set<ID> roleIds) {

        Set<ID> currentRoleIds = new Set<ID>();
    
        // get all of the roles underneath the passed roles
        for(UserRole userRole :[select Id from UserRole where ParentRoleId 
             IN :roleIds AND ParentRoleID != null]) {
            currentRoleIds.add(userRole.Id);
        }
    
        // go fetch some more rolls!
        if(currentRoleIds.size() > 0) {
            currentRoleIds.addAll(getAllSubRoleIds(currentRoleIds));
        }
    
        return currentRoleIds;
    }
    // To get all Parent Roles.
    public static Set<ID> getParentRoleId(Set<ID> roleIds) {

        Set<ID> currentRoleIds = new Set<ID>();
    
        
        // get all of the parent roles.
        for(UserRole ur :[select Id, ParentRoleId from UserRole where Id IN: roleIds]) {
            currentRoleIds.add(ur.ParentRoleId);
        }
    
        // go fetch some more rolls!
        if(currentRoleIds.size() > 0) {
            currentRoleIds.addAll(getParentRoleId(currentRoleIds));
        }
    
        return currentRoleIds;
    }
}

Now go to Developer Console and execute the below code:
  
user gurs = [Select UserRoleId,Email,ForecastEnabled 
                     from user where id = :UserInfo.getUserId() LIMIT 1];

Set<Id> userIdSet = new Set<Id>();
userIdSet.add(gurs.UserRoleId);
Set<ID> finalUserIdSet = Utility.getParentRoleId(userIdSet);


List<UserRole> urList = [Select Id, Name from UserRole where Id IN: finalUserIdSet];
for(UserRole ur: urList) {
    System.debug('------Role Name:'+ur.Name);
}

I also tested the above code and it is working fine.

Regards,
Mahesh

All Answers

Mahesh DMahesh D
Hi Sudhir,

Please find the below code:
  
private static Set<ID> getAllSubRoleIds(Set<ID> roleIds) {

    Set<ID> currentRoleIds = new Set<ID>();

    // get all of the roles underneath the passed roles
    for(UserRole userRole :[select Id from UserRole where ParentRoleId 
         IN :roleIds AND ParentRoleID != null]) {
        currentRoleIds.add(userRole.Id);
    }

    // go fetch some more rolls!
    if(currentRoleIds.size() > 0) {
        currentRoleIds.addAll(getAllSubRoleIds(currentRoleIds));
    }

    return currentRoleIds;
}

Also look into below URL:

http://blog.jeffdouglas.com/2011/02/15/find-my-salesforce-users-by-role-hierarchy/

http://salesforce.stackexchange.com/questions/924/is-there-a-way-to-query-role-hierarchy

Please let me know if it helps you.

Regards,
Mahesh
sudhirn@merunetworks.comsudhirn@merunetworks.com
Thanks Mahesh I am new to salesforce class can you guide me how to execute the above function in developer console to test and see if the code is working as expected 

Thanks
Sudhir
Mahesh DMahesh D
Hi Sudhir,

Please copy and paste the below code by going to Setup --> Apex Classes --> New
  
public class Utility {
    // To get all sub roles.
    public static Set<ID> getAllSubRoleIds(Set<ID> roleIds) {

        Set<ID> currentRoleIds = new Set<ID>();
    
        // get all of the roles underneath the passed roles
        for(UserRole userRole :[select Id from UserRole where ParentRoleId 
             IN :roleIds AND ParentRoleID != null]) {
            currentRoleIds.add(userRole.Id);
        }
    
        // go fetch some more rolls!
        if(currentRoleIds.size() > 0) {
            currentRoleIds.addAll(getAllSubRoleIds(currentRoleIds));
        }
    
        return currentRoleIds;
    }
    // To get all Parent Roles.
    public static Set<ID> getParentRoleId(Set<ID> roleIds) {

        Set<ID> currentRoleIds = new Set<ID>();
    
        
        // get all of the parent roles.
        for(UserRole ur :[select Id, ParentRoleId from UserRole where Id IN: roleIds]) {
            currentRoleIds.add(ur.ParentRoleId);
        }
    
        // go fetch some more rolls!
        if(currentRoleIds.size() > 0) {
            currentRoleIds.addAll(getParentRoleId(currentRoleIds));
        }
    
        return currentRoleIds;
    }
}

Now go to Developer Console and execute the below code:
  
user gurs = [Select UserRoleId,Email,ForecastEnabled 
                     from user where id = :UserInfo.getUserId() LIMIT 1];

Set<Id> userIdSet = new Set<Id>();
userIdSet.add(gurs.UserRoleId);
Set<ID> finalUserIdSet = Utility.getParentRoleId(userIdSet);


List<UserRole> urList = [Select Id, Name from UserRole where Id IN: finalUserIdSet];
for(UserRole ur: urList) {
    System.debug('------Role Name:'+ur.Name);
}

I also tested the above code and it is working fine.

Regards,
Mahesh
This was selected as the best answer
Kishore B TKishore B T
Mahesh,
Copy paste the below code in Dev Console and Execute it.  
​
Set<ID> currentRoleIds = new Set<ID>(); 
for(UserRole userRole :[select Id from UserRole where ParentRoleId IN :roleIds AND ParentRoleID != null]) { currentRoleIds.add(userRole.Id); } 
if(currentRoleIds.size() > 0) 
{ currentRoleIds.addAll(getAllSubRoleIds(currentRoleIds)); } 
system.debug('ROLE ID's'+currentRoleIds);


​Please mark this Answered, if this solves your Query.
sudhirn@merunetworks.comsudhirn@merunetworks.com
Thanks Mahesh and Kishor BT it was great help for me
Mahesh DMahesh D
Hi Sudhir,

Please mark it as solved so that it will be healful to others in the future.

Regards,
Mahesh
Hari KundrapuHari Kundrapu
Pass the UserRoleTable as a Map and it will reduce the use of SOQL queries. You can use this one in Global Util methods.

// To get all sub roles.
/*public static Set<ID> getAllSubRoleIds(Set<ID> roleIds) {
Set<ID> currentRoleIds = new Set<ID>();
// get all of the roles underneath the passed roles
for(UserRole userRole :[select Id from UserRole where ParentRoleId
IN :roleIds AND ParentRoleID != null]) {
currentRoleIds.add(userRole.Id);
}
// go fetch some more rolls!
if(currentRoleIds.size() > 0) {
currentRoleIds.addAll(getAllSubRoleIds(currentRoleIds));
}
return currentRoleIds;
}*/
public static Set<ID> getMapSubRoleIds( Map<Id, UserRole> UserRoleInput, Set<ID> roleIds, Boolean getAllSubRoles) {
Set<ID> currentRoleIds = new Set<ID>();
Map<Id,List<Id>> mapUrIdList = new Map<Id,List<Id>>();
System.debug('Passed roleId: '+roleIds);
Map<Id, UserRole> mapUserRole = new Map<Id, UserRole>();
if (UserRoleInput != null && UserRoleInput.size() > 0) {
mapUserRole = UserRoleInput;
}else {
mapUserRole = new Map<Id, UserRole>([Select Id, Name, ParentRoleID from UserRole limit 10000]);
}
for(UserRole ur1 : mapUserRole.values()){
if (ur1.ParentRoleId != null) {
List<Id> usrRoleList = mapUrIdList.get(ur1.ParentRoleId);
if (usrRoleList == null) {
usrRoleList = new List<Id>();
}
usrRoleList.add(ur1.Id);
mapUrIdList.put(ur1.ParentRoleID, usrRoleList);
}
}
// go fetch some more rolls!
if(roleIds.size() > 0) {
currentRoleIds = mapIteratorSubRoleId( mapUrIdList, roleIds, getAllSubRoles);
System.debug('all roleIds: '+currentRoleIds);
System.debug('roleIds: '+roleIds);
currentRoleIds.addAll(roleIds);
}
return currentRoleIds;
}
public static Set<Id> mapIteratorSubRoleId( Map<Id,List<Id>> userRoleTable, Set<Id> mroleIds, Boolean getAllSubRoles){
Set<Id> presentRoleIds = new Set<Id>();
for(Id sUr : mroleIds) {
if(userRoleTable.containsKey(sUr)){
List<Id> tmpListId = userRoleTable.get(sUr);
for(Id lur1tmpListId : tmpListId){
presentRoleIds.add(lur1tmpListId);
}
// presentRoleIds.addAll(userRoleTable.get(sUr).ParentRoleID);
// System.debug('UserRoleId: '+ userRoleTable.get(sUr).Id + ' Name: '+userRoleTable.get(sUr).Name);
}
}
if(presentRoleIds.size() > 0 && getAllSubRoles == true) {
System.debug('1PresentRoleIds: '+presentRoleIds);
presentRoleIds.addAll(mapIteratorSubRoleId(userRoleTable,presentRoleIds, getAllSubRoles));
System.debug('2PresentRoleIds: '+presentRoleIds);
}
return presentRoleIds;
}
/**
@ get parent roles of a given UserRole
@ Input : UserRole Id's
@ Output: Set<UserRoleId>
@ Added by: Hari Kundrapu 17-05-2019
**/
// To get all Parent Roles.
/*public static Set<ID> getParentRoleId(Set<ID> roleIds) {
Set<ID> currentRoleIds = new Set<ID>();
// get all of the parent roles.
for(UserRole ur :[select Id, ParentRoleId from UserRole where Id IN: roleIds]) {
currentRoleIds.add(ur.ParentRoleId);
}
// go fetch some more rolls!
if(currentRoleIds.size() > 0) {
currentRoleIds.addAll(getParentRoleId(currentRoleIds));
}
return currentRoleIds;
}*/
public static Set<ID> getMapParentRoleId(Map<Id, UserRole> UserRoleInput1, Set<ID> roleIds, Boolean getAllParentRoles ) {
Set<ID> currentRoleIds = new Set<ID>();
Map<Id, UserRole> mapUserRole1 = new Map<Id, UserRole>();
if (UserRoleInput1 != null && UserRoleInput1.size() > 0) {
mapUserRole1 = UserRoleInput1;
}else {
mapUserRole1 = new Map<Id, UserRole>([Select Id, Name, ParentRoleID from UserRole limit 10000]);
}
// Map<Id, UserRole> mapUserRole1 = new Map<Id, UserRole>([Select Id, Name, ParentRoleID from UserRole limit 10000]);
// go fetch some more rolls!
if(roleIds.size() > 0) {
currentRoleIds = mapIteratorParentRoleId(mapUserRole1,roleIds, getAllParentRoles);
currentRoleIds.addAll(roleIds);
}
return currentRoleIds;
}
public static Set<Id> mapIteratorParentRoleId(Map<Id, UserRole> userRoleTable, Set<Id> mroleIds, Boolean getAllParentRoles){
Set<Id> presentRoleIds = new Set<Id>();
for(Id sUr : mroleIds) {
UserRole utmp = userRoleTable.get(sUr);
System.debug('UserRole Name: '+utmp.Name);
if(String.isNotBlank(utmp.ParentRoleID)) {
Id tmpurId = utmp.ParentRoleID;
presentRoleIds.add(tmpurId);
}
}
if(presentRoleIds.size() > 0 && getAllParentRoles == true) {
presentRoleIds.addAll(mapIteratorParentRoleId(userRoleTable,presentRoleIds,getAllParentRoles));
}
return presentRoleIds;
}
 
Karthik ManoKarthik Mano
If you don't like to have SOQL inside iteration ->

public static Set<Id> getAllChildRoleIds(Id parentRoleId) {
        Map<Id,List<Id>> parentChildRoleIdMap = queryAllDBRoles();
        Set<Id> allChildRoleSet = doSubRoleSearch(parentChildRoleIdMap, new Set<Id>{parentRoleId}, new Set<Id>());
        return allChildRoleSet;
    }
    public static Set<Id> doSubRoleSearch(Map<Id,List<Id>> parentChildRoleIdMap, Set<Id> parentRoleIdSet, Set<Id> cumulativeRoleIdSet) {
        Set<Id> queryMoreSet = new Set<Id>();
        for(Id pRoleId:parentRoleIdSet) {
            queryMoreSet = new Set<Id>();
            if(!parentChildRoleIdMap.containsKey(pRoleId)) {
                continue;
            }
            for(Id cRoleId:parentChildRoleIdMap.get(pRoleId)) {
                cumulativeRoleIdSet.add(cRoleId);
                queryMoreSet.add(cRoleId);
            }
        }
        if(!queryMoreSet.isEmpty()) {
            doSubRoleSearch(parentChildRoleIdMap, queryMoreSet, cumulativeRoleIdSet);
        }
        return cumulativeRoleIdSet;
    }
    public static Map<Id,List<Id>> queryAllDBRoles() {
        Map<Id,List<Id>> parentChildRoleIdMap = new Map<Id,List<Id>>();
        for(UserRole uRole:[SELECT Id,ParentRoleId FROM UserRole]) {
            if(uRole.ParentRoleId == NULL) {
                continue;
            }
            if(parentChildRoleIdMap.containsKey(uRole.ParentRoleId)) {
                List<Id> tempIdList = new List<Id>();
                tempIdList.addAll(parentChildRoleIdMap.get(uRole.ParentRoleId));
                tempIdList.add(uRole.Id);
                parentChildRoleIdMap.put(uRole.ParentRoleId, tempIdList);
            }else {
                parentChildRoleIdMap.put(uRole.ParentRoleId, new List<Id>{uRole.Id});
            }
        }
        return parentChildRoleIdMap;
    }
Bhaskar Muli 6Bhaskar Muli 6
Hi Mahesh,
Do you have test class for that code.getallsubroleIds and getrolesubbordinateusers methods? If,yes please send here