2

How can I return 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.

6

Well, it's actually a problem with concepts. The thing is you're trying to bring User records, but in them a Group field that doesn't actually exist.

Check the User API

After having obtained the users, if you were to obtain the group to which the user belongs, you can achieve it through different paths:

  1. 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<User>  groupAUsers = [SELECT User.Id, User.Email FROMUser WHERE Id IN (SELECT UserOrGroupId FROM GroupMember WHERE Group.Name = 'GROUP A)]`
    
  2. 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. Afterwards 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<Id>(new Map<Id, User>(users).keySet());
    // And now thet the groupmembers.
    GroupMember[] groupMembers = [SELECT Group.Name, UserOrGroupId FROM GroupMember WHERE UserOrGroupId IN :users];
    
3
  • I am trying to minimise the number of queries. I was hoping to be able to effectively join these objects. Some of my scenarios involve objects with dynamic roll-ups and the query count is killing me. – Dizzley Sep 13 '17 at 9:44
  • That would sum a total of 2 queries in the second case... maybe you should optimize in another place, wherever the big amount of queries would be done. – Javier García Manzano Sep 13 '17 at 9:50
  • Re concepts: the 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. – Dizzley Sep 13 '17 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.