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!

score 0 · Accepted Answer · edited May 23 '17 at 12:09

0

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?

edited May 23 '17 at 12:09

Community

1
1

answered Feb 24 '17 at 20:38

Egor

1,622
12
26

Thanks for the comment! I will work with getting multiple levels of children like in your first soql example. We are thinking about applying a 5ish layer limit to the recursive calls anyway, so using that method it seems I could do it in one shot. But right now, my worst case run time on load is O(n^3) solely due to this group member thing, if I can reduce these, it should be O(n^2), which I don't think there is anyway to get it lower than that when dealing with what groups are in what events due to salesforce having no bridge between groups and events :( – Tyler Dahle Feb 24 '17 at 21:14
Is the syntax supposed to be Group.Group.GroupId? It doesn't recognize that relationship. It seems Group has child object of GroupMember, but GroupMember doesn't seem to have a visible parent object of a group... at least what I can see from the salesforce workbench site when viewing these SObjects. – Tyler Dahle Feb 24 '17 at 22:02
@TylerDahle I think I misunderstood, I was under the impression that GroupId is a reference to other members (that is, a recursive reference to itself, similar to the "parent account" field on Account). It looks like GroupMember is a joining object that facilities a many-to-many relationship, but there's no direct parent-child link. Sadly SOQL doesn't support inner joins on the same object type (for whatever reason) so I don't see any way of getting multiple levels in one query. Sorry, I was wrong there :( – Egor Feb 24 '17 at 22:47
No problem. Yeah, I am not seeing how to get away from getting all groups and all group members, then looping through to match Id and groupId, which may run into 10 second limit, or my recursive group member function, which will easily run into 100 query limit... Perhaps the 10 second limit will be less likely to occur? – Tyler Dahle Feb 24 '17 at 22:49
If you check out the article I linked, they talk about an O(N) algorithm to build trees out of a flat structure of nodes with parent Ids, which is exactly what you have. With O(N) complexity 10 seconds should be enough for a very large data set, IMO. – Egor Feb 24 '17 at 22:54
I'll look at it more. A big issue is there is no direct link between Group and its GroupMembers... GroupMembers have GroupId which matches Group's Id field that they are a part of so I still don't know if it would get away from nested loops. – Tyler Dahle Feb 24 '17 at 23:03
The idea is to pull all GroupMembers in a single query `[SELECT GroupId, UserOrGroupId FROM GroupMember]` and then go through them just once to figure out which Groups are children of the group you are actually interested in. This should give you all the Group ids that you want in just one loop. – Egor Feb 24 '17 at 23:07
Actually, it still doesn't seem like it is going to reduce to O(N), since I am interested in ALL groups, I need all group members for all groups, thus I need an outer loop going through each group, then still an inner loop going through all group members to match their groupIds to the currently looked at group's Id. – Tyler Dahle Feb 27 '17 at 15:20

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

1 Answers1