Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
jleehjleeh 

How to prevent SOQL List from displaying duplicates?

Hi - I've created an SOQL query on the GroupMember object to query for all Queues that a user is not a member of. However, the list populates duplicates. 

Does anyone know if the query can be setup to not display duplicates in my List? I've read that a loop may be possible, but I don't know how I would set it up to remove duplicates.


notqueueresults = new List<GroupMember> ([SELECT ID,Group.Name FROM GroupMember WHERE UserOrGroupId !=: results[0].Id ])

Thanks!
Best Answer chosen by jleeh
logontokartiklogontokartik
Hey Justin, try the below,

public class QueueManagementController {

    String searchText;
    List<User> results;
    List<GroupMember> queueresults;
    List<Group> notqueueresults; // Change this to a List<Group>

    public String getSearchText() {
        return searchText;
    }

    public void setSearchText(String s) {
        searchText = s;
    }

    public List<User> getResults() {
        return results;
    }

    public List<GroupMember> getQueueResults() {
        return queueresults;
    }
    
    public List<GroupMember> getnotQueueResults() {
        return notqueueresults;
    }

    public PageReference doSearch() {

//Find user based on name input from VF page

        results = new List<User>([SELECT Id, Name, Email FROM User WHERE Name =:searchText LIMIT 1]);

//Find Queues that the User is a member in. Current issue is that it pulls both Queues and Groups

        queueresults = new List<GroupMember>([SELECT ID,Group.Name, GroupId 
                                              FROM GroupMember 
                                              WHERE UserOrGroupId =: results[0].Id ]);
        
// Add the Set of Ids in which the User is already member 
        Set<Id> presentQueueIds = new Set<Id>();
        for(GroupMember gm : queueresults){
            presentQueueIds.add(gm.GroupId);
        }

//Find Queues that the User is not a member of. Current issue is that it doesn't pull data. 

        notqueueresults = new List<Group> ([SELECT Id, Name 
                                                        FROM Group 
                                                        WHERE Type IN ('Queue','Regular') 
                                                        AND ID NOT IN :presentQueueIds);


        
        return null;
    }
        
}

All Answers

Hargobind_SinghHargobind_Singh

Hi Justin, 

Salesforce doesn't provide grouping in query result yet. I'm afraid you migth have to programmatically remove duplicates. One option is to fill up a set with group.name so that only unique values are stored:
Set<String> groupNameSet = new Set<String>(); 
for( GroupMember gm: [SELECT ID,Group.Name FROM GroupMember WHERE UserOrGroupId !=: results[0].Id ]){
  groupNameSet.add(gm.Group.Name); 
}
logontokartiklogontokartik
Hi can you please let us know which field you want to use to de-dupe? Do you want unique group names? You can use AggregateResult if you want group names

You can do something like 


List<AggregateResult> groupResults = new List<AggregateResult>();
groupResults = [Select Group.Name groupName from GroupMember where UserOrGroupId = :results[0].Id group by Group.Name];

for(AggregateResult agg : groupResults){
   
    system.debug('Group Names ' + agg.get('groupName'));

}
jleehjleeh
logontokartik - Yes, I want to dedupe by Group.Name

Now that I'm looking at all the queue's in the org, I'm curious how there are multiple ID's for each GroupID. Is this the way Public Groups and Queue's share the GroupMembers object?
logontokartiklogontokartik
Yes, there is a also a separate object call Group which will basically give you all the groups in your org and there is a type field on Group object which tells us what type of group it is if its a Queue, a Role or Regular etc. More info in the below link

https://www.salesforce.com/us/developer/docs/api/Content/sforce_api_objects_group.htm
Hargobind_SinghHargobind_Singh
here is another option to get you unique groupnames: 


List<Group> notqueueresults = new List<Group> ([SELECT Id, Name FROM Group where ID IN (select GroupID from GroupMember WHERE UserOrGroupId !=: results[0].Id)  ])
jleehjleeh
Harobind_singh - I tried this code, and while it doesn't throw any errors, it also doesn't pull any records either. Including the full controller below. 

public class QueueManagementController {

    String searchText;
    List<User> results;
    List<GroupMember> queueresults;
    List<GroupMember> notqueueresults;

    public String getSearchText() {
        return searchText;
    }

    public void setSearchText(String s) {
        searchText = s;
    }

    public List<User> getResults() {
        return results;
    }

    public List<GroupMember> getQueueResults() {
        return queueresults;
    }
    
    public List<GroupMember> getnotQueueResults() {
        return notqueueresults;
    }

    public PageReference doSearch() {

//Find user based on name input from VF page

        results = new List<User>([SELECT Id, Name, Email FROM User WHERE Name =:searchText LIMIT 1]);

//Find Queues that the User is a member in. Current issue is that it pulls both Queues and Groups

        queueresults = new List<GroupMember>([SELECT ID,Group.Name 
                                              FROM GroupMember 
                                              WHERE UserOrGroupId =: results[0].Id ]);
        
//Find Queues that the User is not a member of. Current issue is that it doesn't pull data. 

        List<Group> notqueueresults = new List<Group> ([SELECT Id, Group.Name 
                                                        FROM Group 
                                                        WHERE Type='Queue' 
                                                        AND ID IN 
                                                        (SELECT GroupId FROM GroupMember WHERE UserOrGroupId =: results[0].Id)  ]);


        
        return null;
    }
        
}
Hargobind_SinghHargobind_Singh
Hi Justin,

Line 46, do you want to change the condition to UserOrGroupID "is NOT equal to" your chosen ID ? seems like you are checking if it is Equal. Can you add a "NOT" to it: 

UserOrGroupId != : results[0].Id
jleehjleeh
Yes, I tried with both "=" and "!=" to see if I could get any results, but this doesn't work either. Line 36 pulls correctly, but it does pull both groups and queues. 
logontokartiklogontokartik
Hey Justin, try the below,

public class QueueManagementController {

    String searchText;
    List<User> results;
    List<GroupMember> queueresults;
    List<Group> notqueueresults; // Change this to a List<Group>

    public String getSearchText() {
        return searchText;
    }

    public void setSearchText(String s) {
        searchText = s;
    }

    public List<User> getResults() {
        return results;
    }

    public List<GroupMember> getQueueResults() {
        return queueresults;
    }
    
    public List<GroupMember> getnotQueueResults() {
        return notqueueresults;
    }

    public PageReference doSearch() {

//Find user based on name input from VF page

        results = new List<User>([SELECT Id, Name, Email FROM User WHERE Name =:searchText LIMIT 1]);

//Find Queues that the User is a member in. Current issue is that it pulls both Queues and Groups

        queueresults = new List<GroupMember>([SELECT ID,Group.Name, GroupId 
                                              FROM GroupMember 
                                              WHERE UserOrGroupId =: results[0].Id ]);
        
// Add the Set of Ids in which the User is already member 
        Set<Id> presentQueueIds = new Set<Id>();
        for(GroupMember gm : queueresults){
            presentQueueIds.add(gm.GroupId);
        }

//Find Queues that the User is not a member of. Current issue is that it doesn't pull data. 

        notqueueresults = new List<Group> ([SELECT Id, Name 
                                                        FROM Group 
                                                        WHERE Type IN ('Queue','Regular') 
                                                        AND ID NOT IN :presentQueueIds);


        
        return null;
    }
        
}



This was selected as the best answer
logontokartiklogontokartik
Well. which line its throwing the error, I see that I missed a ']' on line 51. since my code is all handwritten, there might be some compile errors, but I am sure i would work.

public class QueueManagementController {

    String searchText;
    List<User> results;
    List<GroupMember> queueresults;
    List<Group> notqueueresults; // Change this to a List<Group>

    public String getSearchText() {
        return searchText;
    }

    public void setSearchText(String s) {
        searchText = s;
    }

    public List<User> getResults() {
        return results;
    }

    public List<GroupMember> getQueueResults() {
        return queueresults;
    }
    
    public List<GroupMember> getnotQueueResults() {
        return notqueueresults;
    }

    public PageReference doSearch() {

//Find user based on name input from VF page

        results = new List<User>([SELECT Id, Name, Email FROM User WHERE Name =:searchText LIMIT 1]);

//Find Queues that the User is a member in. Current issue is that it pulls both Queues and Groups

        queueresults = new List<GroupMember>([SELECT ID,Group.Name, GroupId 
                                              FROM GroupMember 
                                              WHERE UserOrGroupId =: results[0].Id ]);
        
// Add the Set of Ids in which the User is already member 
        Set<Id> presentQueueIds = new Set<Id>();
        for(GroupMember gm : queueresults){
            presentQueueIds.add(gm.GroupId);
        }

//Find Queues that the User is not a member of. Current issue is that it doesn't pull data. 

        notqueueresults = new List<Group> ([SELECT Id, Name 
                                                        FROM Group 
                                                        WHERE Type IN ('Queue','Regular') 
                                                        AND ID NOT IN :presentQueueIds]);


        
        return null;
    }
        
}
jleehjleeh
Hey - No, it wasn't the syntax issues. I found the error on my VF page. I had to tweak the input values to account for a change in the controller. 

Many thanks for everyone's help. Hopefully other people will find this useful as well.