Retrieve group members from a group which are only other groups

governorlimitssoql

SELECT Id, Name, DeveloperName, Type, RelatedId,
        (
            SELECT UserOrGroupId
            FROM GroupMembers
            WHERE UserOrGroupId IN (
                SELECT Id
                FROM Group
                WHERE UserOrGroupId IN (SELECT Id FROM Group)
            )
        )
    FROM Group

I was using the above quite happily on the API however we need to move this over to APEX and that doesn't support nesting semi joins how else can I restrict GroupMembers to only return group members related to this group that are groups and not users. I cannot filter after the fact as there are too many users, and I will hit my governor limits.

EDIT:
I have tried going from the membership out using the below but the issue is the last level of groups don't have group memberships therefore don't show up. As I don't want to query every single group and group membership this isn't really practical.

SELECT Id, Group.Id, Group.Name, Group.DeveloperName,
    Group.OwnerId, Group.Type, Group.RelatedId, Group.Email,
    Group.DoesSendEmailToMembers, Group.DoesIncludeBosses, Group.QueueRoutingConfigId,
    UserOrGroupId
FROM GroupMember
WHERE UserOrGroupId IN (
    SELECT Id
    FROM Group
)

Best Answer

Unless I'm missing something, couldn't it just be:

SELECT Id, Name, DeveloperName, Type, RelatedId,
    (
        SELECT UserOrGroupId
        FROM GroupMembers
        WHERE UserOrGroupId IN ( SELECT Id FROM Group WHERE Type = 'Regular' )
    )
FROM Group

Related Solutions

[SalesForce] Selection of group name and user name from GroupMember in one query

You might take a look at SOQL polymorphism.

Since the parent object referenced by UserOrGroupId could be either a User or a Group, normal SOQL won't let you reference any parent fields, similar to attempting to reference fields via WhatId on an Event. SOQL's TYPEOF keyword should allow you to perform the query you want.

REVISED The TYPEOF keyword would still give you the query you want, but I apologize for overlooking it's general unavailability. Without this being a widely available feature, I do not think there is another way to create the single query on GroupMember.

...

Map<Id, Group> groupMap = new Map<Id, Group>([
    Select Id, Name
    From Group
    Where Type = 'Queue'
]);

List<GroupMember> groupMembers = [
    Select GroupId, UserOrGroupId
    From GroupMember
    Where GroupId In :groupMap.keySet()
];

Set<Id> userOrGroupIds = new Set<Id>();
for (GroupMember member : groupMembers) {
    userOrGroupIds.add(member.UserOrGroupId);
}

Map<Id, User> userMap = new Map<Id, User>([
    Select Id, LastName
    From User
    Where Id In :userOrGroupIds
]);

Map<Id, List<User>> groupIdToUsers = new Map<Id, List<User>>();
for (Id groupId : groupMap.keySet()) {
    groupIdToUsers.put(groupId, new List<User>());
}

for (GroupMember member : groupMembers) {
    if (userMap.containsKey(member.UserOrGroupId)) {
        groupIdToUsersMap.get(member.GroupId).add(userMap.get(member.UserOrGroupId));
    }
}

...

With this, you can loop over the values of groupMap and pull the list of Users from groupIdToUsers.

You can alter your filters and limits in the queries as you see fit, especially if you are expecting a large number of Users. There are also certainly other ways you could write this.

If you want to track which Groups are also members of other Groups, you would need to maintain a separate Group map that gets processed similarly to User map.

[SalesForce] How to find all users with whom the custom object is shared

I dont think there is a current way around this, as the UserRecordAccess object only returns records for which a given user has access, not userid's for the given recordid.

If the purpose is just to show a user who all has access to a record, you can do this through the native interface by passing the current record id into a url like the one below which will take you to a sharing list showing you every user with access to that record (works for any record in any object)

salesforce.com/p/share/SharingBlowoutPage/d?parentId=RecordID

Related Topic