4

I'm used to using SQL to make queries from multiple tables in one query. What I'm trying to do is select all group members and their names where the group is of a type 'Queue'. I'm able to select the UserOrGroupId and the GroupMember.Group.Name and GroupMember.Group.Type to that information. Why can't I go from the UserOrGroupId field directly to the User record? I'd prefer to do this all in one query so I can easily get the group name and user name pair in one look up.

Is there a way that I'm missing somehow?

  • 3
    Can you show us how you've attempted to form your SOQL query to this point? Once you do, I suspect you'll get a much better response. Without seeing it, readers won't know exactly what custom relationships you're working with, or what info you have that you're starting with, to really be able to help you. – crmprogdev Jun 1 '13 at 3:13
  • The problem is resolving the User through the UserOrGroupId field. Since this can be either, the SOQL doesn't natively figure out the link. I can't use the TYPEOF key word because that is not enabled for the organization(s) I use for either testing or production. – Robert Harper Jun 3 '13 at 21:05
3

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.

  • It looks like that may not be an option. When I try to add the TYPEOF to my query, I get a message that sates that that feature is not enabled in my organization. – Robert Harper Jun 3 '13 at 21:01
  • 1
    Now after looking up documentation on TYPEOF, I'm not sure why you suggest it. Since it is in "Developer Preview" and not in general release, it isn't all that helpful. Cool idea once it is released. Any idea when? – Robert Harper Jun 3 '13 at 21:20
  • You are absolutely right, my apologies. I didn't find anything indicating when this will be a widely available feature either. To actually answer your question, without the typeof keyword i don't believe there is anyway you can do the query that you want. You will need to assemble a set of the UserOrGroupIds and then do separate queries on User and Group. – Thomas Crouse Jun 3 '13 at 21:42
  • The problem I end up with is that there are over 140 groups so when I gather the list of groups, gathering the users in each group puts me over the 100 query threshold. I think the managers might prefer a report but I've managed to create a site where they can select a given group and then the page will list the users in that group. We'll have to see if this is OK with the target users. – Robert Harper Jun 4 '13 at 12:34
  • You should be able to restructure your code to only perform a few queries. If you can add your current code to your original post, I'll gladly help take a look and we can figure out how to rewrite it. – Thomas Crouse Jun 4 '13 at 13:09

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.