Avoiding salesforce governing limits on soql queries getting group members for each group?

Question

I am working in apex on salesforce platform. I have this loop to grab all group names, Ids, and their respective group members, place them in an object to collect all this info, then put that in a list to have a list of all groups and all information I need:

List<groupInfo> memberList = new List<groupInfo>();
    for(Id key : groupMap.keySet()){
        groupInfo newGroup = new groupInfo();
        Group g = groupMap.get(key);
        if(g.Name != null){
            set<Id> memberSet = getGroupEventRelations(new set<Id>{g.Id});
            if(memberSet.size() != 0){
                newGroup.groupId = g.Id;
                newGroup.groupName = g.Name;
                newGroup.groupMemberIds = memberSet;
                memberList.add(newGroup);
            }
        }
    }

My getGroupEventRelations method is as such:

global static set<Id> getGroupEventRelations(set<Id> groupIds){
    set<Id> nestedIds = new set<Id>();
    set<Id> returnIds = new set<Id>();
    List<GroupMember> members = [SELECT Id, GroupId, UserOrGroupId FROM GroupMember WHERE GroupId IN :groupIds];
    for(GroupMember member : members){
        if(Schema.Group.SObjectType == member.UserOrGroupId.getSObjectType()){
            nestedIds.add(member.UserOrGroupId);
        } else{
            returnIds.add(member.UserOrGroupId);
        }
    }

    if(nestedIds.size() > 0){
        returnIds.addAll(getGroupEventRelations(nestedIds));
    }
    return returnIds;
}

getGroupEventRelations contains a soql query, and considering this is called inside a loop of groups... if someone has over 100 groups with group members or possibly a series of 100 nested groups inside groups... then this is going to hit the governing limits of salesforce soql queries pretty quickly...

I am wondering if anyone knows of a way to possibly get rid of the soql query inside getGroupEventRelations to get rid of the query in the loop. When I want group members for a specific group, I am not really seeing a way to get by this without more loops inside loops where I could risk running into CPU timeout salesforce governing limit :(

Thank you in advance for any help!

Egor Egor · Accepted Answer · 2017-02-24T20:38:44

At large enough numbers there's no solution, you'll run into SOME governor limit. But you can certainly make your code work with bigger numbers than it does now. Here's a quick little cheat you could do to cut nesting 5-fold. Instead of just looking at the immediate parent (single level of children) look for parent, grandparent, great grandparent, etc, all in one query.

[SELECT Id, GroupId, UserOrGroupId FROM GroupMember WHERE (GroupId IN :groupIds OR Group.GroupId IN :groupIds OR Group.Group.GroupId IN :groupIds OR Group.Group.Group.GroupId IN :groupIds OR Group.Group.Group.Group.GroupId IN :groupIds OR Group.Group.Group.Group.Group.GroupId IN :groupIds) AND Id NOT IN :returnIds];

You just got 5 (or is it 6?) levels of children in one SOQL call, so you can support that many times more nest levels now. Note that I added a 'NOT IN' clause to make sure you don't repeat children that you already have, since you won't know which Ids came from the bottom level.

You can also make your very first call for all groups instead of each group at a time. So if someone has 100 groups you'll make just one call instead of 100.

List<Group> groups = groupMap.values();
List<GroupMember> allMembers = [SELECT Id, GroupId, UserOrGroupId FROM GroupMember WHERE GroupId IN :groups];

Lastly, you could query all GroupMembers in a single SOQL call and then iterate yourself. Like you said, you risk running into the 10 second limit here, but if the number of groups isn't in the millions you'll likely be just fine, especially if you do some O(n) analysis and choose good data structures and algorithms. On the plus side, you won't have to worry about SOQL limits regardless of the nesting and the tree complexity. This answer should be very helpful, they are doing almost exactly what you'd have to do if you pulled all members in one call. How to efficiently build a tree from a flat structure?

Avoiding salesforce governing limits on soql queries getting group members for each group?

1 Answers