0
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
)

1 Answer 1

1

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
4
  • Nope can't have semi joins in child queries in apex that's what I was doing before it works if you call the restpoint query but not in apex it's self Commented Aug 1, 2023 at 11:26
  • @KarlLivesey This works for me in apex code. What error are you getting when you use this?
    – Nick C
    Commented Aug 2, 2023 at 1:15
  • hmmmm odd it was giving me that error when I was trying to deploy that I couldn't do that in child queries hence why I went on this adventure Commented Aug 2, 2023 at 6:49
  • I think yours had an extra sub query
    – Nick C
    Commented Aug 2, 2023 at 7:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .