Tag Archives: SOQL

Distinct values in APEX SOQL query

I’ve requirement in Visualforce page to show suggestive search i.e. as soon I start typing,
It should start suggesting names. I’m going to write a separate article specifically for this.

But here I’m going to discuss small issue :
Although ‘suggestive search’ was working properly, but if in controller, if same name exist multiple times ( say, I was searching Doctors by combining their first name and last name) , then auto suggest was giving names multiple times.
So my requirement was- back-end SOQL query should return distinct records.
My query was like this :

List<xyz__c> provlist =  Database.query(‘Select id ,  First_Name__c,  Last_Name__c ,Phone__c, Email_Address__c  from xyz__c’);

I had choice to use aggregate result but was not able to find way to convert aggregate Result into list  as ultimately  in VF, I need to iterate list. compex java script for auto suggest  was also needing list

I was able to solve the issue with following simple code :

declare a new final list of same object  and declare a set of string :

List<xyz__c> provlistFinal = new list<xyz__c> ();                                      List<string> fullnameSet = new set<string>();

Iterate original list and populate new list using some criteria based on ‘set’ as shown below

For(xyz__c p : provlist) 
          { 
            if(!fullnameSet.contains(p.First_Name__c + ' ' + p.Last_Name__c))
               { 
                 fullnameSet.add(p.First_Name__c + ' ' + p.Last_Name__c);
                 provlistFinal.add(P);
               }
          }

So above code if populating new list(provlistFinal) if set doesn’t already contain same name(made by concatenating first name and last name

This new list could be used for iteration in VF page.