How to join User, GroupMember and Group in SOQL?
How can I return the User.Email and Group. Name from SOQL? I want the group names and the email addresses of their members from a query like:
SELECT User.Id, User.Email, Group. Name from User WHERE Id IN (SELECT UserOrGroupId FROM GroupMember WHERE Group.Name IN ('Group A', 'Group B'))Obviously, my query is incorrect but I can't see how.
It is conceptual people as you're trying to bring User records but in them a Group field that doesn't actually exist. GroupMember object contains a row for each User in the group. Each record contains (FK UserOrGroupId into User), and (FK GroupId into Group). It seems that my grasp of concepts is OK, but its not so easy to get the information from the two related objects. The Group is really the drivng object for the results set I want - I want the email addresses of users in the groups of interest. After having obtained the users, if you were to obtain the group to which the user belongs, you can achieve it through different paths: The first is to make one query for each group, that way you will have different lists of users, having each list the users of that specific group:
List groupAUsers = [SELECT User.Id, User.Email FROMUser WHERE Id IN (SELECT UserOrGroupId FROM GroupMember WHERE Group.Name = 'GROUP A)]`The second option, and the desired one in case you want to perform this with many groups, is to first query for the Users and map them by Id. Afterward, query GroupMembers filtered by the previous user list. Then just play with your data as desired.
// Get the users. User[] users = [SELECT User.Id, User.Email FROM User WHERE Id IN (SELECT UserOrGroupId FROM GroupMember WHERE Group.Name IN ('Group A', 'Group B'))]; // Get the Ids. Id[] userIds = new List(new Map(users).keySet()); // And now thet the groupmembers. GroupMember[] groupMembers = [SELECT Group.Name, UserOrGroupId FROM GroupMember WHERE UserOrGroupId IN :users];