On one of my recent  assignments, I had the distinct pleasure to dive deep into Salesforce Groups (Public Groups et al) and Group Members. While most SFDC developers will be familiar with the concept of Public Groups in SFDC, I found it interesting how SFDC actually stores this relationship in its tables.

To start with some background, in Salesforce you can define Public Groups by going to Setup–>Manage Users (Administration Setup) –> Public Groups. A Public Group can be composed of one of the following entities:

•  Users
• Territories
• Territories and sub-ordinates
• Roles
• Roles and sub-ordinates
• Public Groups

Behind the scenes, each of these entities except Users essentially gets assigned a Group Id in the Group table. Each Territory will be assigned two Groups Ids – one for the Territory itself and one to denote the “Territories and sub-ordinates” for that Territory. A Public Group is defined with a Type value of Regular in the Group table. Here’s a typical snapshot from a Group table:

A GroupMember record exists for every User or Group who is a direct member of a public group whose Type field is set to Regular. User records that are indirect members of Regular public groups are not listed as group members. A User can be an indirect member of a group if he or she is in a UserRole above the direct group member in the hierarchy, or if he or she is a member of a group that is included as a subgroup in that group.

So, if we were to simply figure out what groups a User belongs to, it is not simply a matter of joining Group and GroupMember table (and given the limited joining capabilities in SFDC, it becomes even more challenging). Also, note that the user can be a member of multiple Public Groups. A user could be in a Public Group because of the following reasons:

• A User is a direct member of the Public Group – this is a simple case where the GroupMember table will have a direct mapping. GroupMember.UserOrGroupId will be the 18-character SFDC ID of the User and the GroupId will be the SFDC ID of the Group.
• A User is  assigned to a Territory or to a sub of a Territory  assigned to a Public Group – In this case, we first need to look into the UserTerritory table to figure out the Territories directly assigned to the User. Additionally, we also need to traverse up the tree (using Territory.ParentTerritoryId) and figure out all the direct and indirect ancestors.
• A User is assigned to a Role or to a sub of a Role assigned to a Public Group – Same concept as the Territories, however, we need to go to the UserRole table in this case.
• A User is assigned to a Public Group which in turn is assigned to another Public Group (and these can be nested into multiple layers) – Here again, similar in concept to Roles and Territories, where we would have to traverse up the tree to figure out what are the Parent Groups for the directly assigned Public Group. However, to do this, we would first have to get all the directly and indirectly assigned Public Groups by virtue of Roles and Territories and Users and then use this information to figure out the Public Group hierarchies.

So let’s start writing some code to figure out how we can go about getting not only the directly assigned Public Groups for a User, but also the indirectly assigned ones.

Let’s first get all the Territory and Role Ids to which the user is assigned, either directly or indirectly. Note that ALL_RELATED_TERRITORY_IDS and ALL_RELATED_ROLE_IDS are Set<ID> types.

ALL_RELATED_TERRITORY_IDS = getParentTerritoryIDs(UserInfo.getUserId()); //
ALL_RELATED_ROLE_IDS = getParentRoleIDs(UserInfo.getUserRoleId());

Since both the Territories and Roles will exist as RelatedIds in the Group table, lets move all of these into a Set so that we can use later in a SOQL against the Group table.

allRelatedIds.addAll(ALL_RELATED_TERRITORY_IDS);
allRelatedIds.addAll(ALL_RELATED_ROLE_IDS);

Now lets get the Group Ids – now we are not only getting the directly assigned groups, but also the indirectly assigned ones

Map<Id, Group> userGroups = new Map<Id, Group> (  [Select Id, RelatedId  from Group where RelatedId in: allRelatedIds]);
userAssociatedGroups.addAll(userGroups.keySet());

Now that we have all the group ids to cover for any hierarchical Territory and Role Ids that make up Public Groups, check and add any direct or indirect hierarchical Public Groups (Public Group inside a Public group inside a P…)

ALL_RELATED_PUBLIC_GROUP_IDS = getParentPublicGroupIDs(userAssociatedGroups, userId);
System.debug('ALL_RELATED_PUBLIC_GROUP_IDS = ' + ALL_RELATED_PUBLIC_GROUP_IDS); userAssociatedGroups.addAll(ALL_RELATED_PUBLIC_GROUP_IDS);

Phew! So now we have all possible Public Groups the User could possibly belong to (until Salesforce decides to add more Types that can be Public Grouped). The final step would be to take this information and join it with the GroupMember table. The key helper methods that actually traverse the Territory, role and Public Group trees are all given at the end of this post.

for(GroupMember aGrp: [SELECT GroupId, Group.Name, Id, SystemModstamp, UserOrGroupId FROM GroupMember
where UserOrGroupId in :userAssociatedGroups]){
userAssociatedPublicGroups.add(aGrp.GroupId);
}


The Helper Methods to traverse the Territory, Role and Public Group Hierarchies
/*
 * @description - This method will return a Set of IDs that includes
 * 1. All direct territories that the user belongs to
 * 2. All parent and grand parents traversed through the top till there is no parent
 */
 public static Set<ID> getParentTerritoryIDs(ID userID){
 List<UserTerritory> userTerritories = [Select Id, TerritoryId, UserId 
 from UserTerritory
 where UserId = :userID];
 if(userTerritories==null || userTerritories.size() == 0 ){
 return null;
 }
 Set<ID> allRelatedTerrIDs = new Set<ID> ();
 Set<ID> directTerrIds = new Set<ID> ();
 for(UserTerritory ut: userTerritories){
 directTerrIds.add(ut.TerritoryId);
 }
 //load up ALL Territories
 Map<Id, Territory> territoryMap = new Map <ID, Territory> ([Select Id, ParentTerritoryId, Name
 from Territory]);

 for(ID terrId: directTerrIds){
 allRelatedTerrIDs.add(terrId);
 Territory aTerr = territoryMap.get(terrID);
 while(aTerr.ParentTerritoryId!=null){//if there is no Parent, then it should drop out of the loop
 allRelatedTerrIDs.add(aTerr.ParentTerritoryId);//record all related Terr Ids in this set
 aTerr = territoryMap.get(aTerr.ParentTerritoryId);//this will now change the reference to th Parent Territory
 }
 }
 return allRelatedTerrIDs ; 
 }

/*
 * @description - This method will return a Set of IDs that includes
 * 1. All direct Public Groups that the user belongs to
 * 2. All parent and grand parents traversed through the top till there is no parent
 */
 public static Set<ID> getParentPublicGroupIDs(Set<ID> groupIds, ID userId){
 List<GroupMember> userGroupMembers = [Select Id, GroupId, Group.Type, UserOrGroupId 
 from GroupMember
 where (UserOrGroupId in: groupIds OR UserOrGroupId =: userId)
 and Group.Type = 'Regular'];
 if(userGroupMembers==null || userGroupMembers.size() == 0 ){
 return null;
 }
 Set<ID> allRelatedPubGrpIDs = new Set<ID> ();
 Set<ID> directPubGrpIds = new Set<ID> ();
 for(GroupMember gm: userGroupMembers){
 directPubGrpIds.add(gm.GroupId);
 }
 //load up ALL GroupMembers that are related to Public Groups
 List<GroupMember> groupMembers = new List <GroupMember> ([Select Id, GroupId, Group.Type, UserOrGroupId 
 from GroupMember
 where Group.Type = 'Regular']);

 Map<Id, GroupMember> userGrpIdToGroupMember = new Map<Id, GroupMember> ();
 for(GroupMember aMem: groupMembers){
 userGrpIdToGroupMember.put(aMem.UserOrGroupId, aMem);
 }

 for(GroupMember grpMember: userGroupMembers){
 allRelatedPubGrpIDs.add(grpMember.GroupId);//add the direct Group Id to relatedId list
 GroupMember aGrpMem = userGrpIdToGroupMember.get(grpMember.GroupId);//Check if this Group itself belongs to another Public Group
 while(aGrpMem!=null){//if there is no associated Public Group, then it should drop out of the loop
 allRelatedPubGrpIDs.add(aGrpMem.GroupId);//record all related Terr Ids in this set
 aGrpMem = userGrpIdToGroupMember.get(aGrpMem.GroupId);//this will now change the reference to th Parent Group, if any
 }
 }
 return allRelatedPubGrpIDs ; 
 }

/*
 * @description - This method will return a Set of IDs that includes
 * 1. All direct roles that the user belongs to
 * 2. All parent and grand parents roles traversed through the top till there is no parent
 */
 public static Set<ID> getParentRoleIDs(ID roleId){

 if(roleId==null){
 return null;
 }
 Set<ID> allRelatedRoleIDs = new Set<ID> ();
 //load up ALL Roles
 Map<Id, UserRole> roleMap = new Map <ID, UserRole> ([Select Id, ParentRoleId, Name
 from UserRole]);
 if(roleMap == null || roleMap.size() == 0 ){
 return null;
 }
 Id tmpRoleId = roleId;
 do{

 UserRole theRole = roleMap.get(tmpRoleId);
 allRelatedRoleIDs.add(theRole.Id);
 tmpRoleId = theRole.ParentRoleId;

 }while (tmpRoleId!=null);

 return allRelatedRoleIDs ; 
 }