0
String leadPhone = lead.phone;
String leadEmail = lead.email;
String qry = 'SELECT a.Id, a.Name,' 
             +'a.Email__c,a.Birth_Date__c,a.Mobile_Phone_Number__c,'
             +'a.Residence_Number__c,a.Work_Number__c'
             +' From Account a WHERE';

In the above query string and I wants to search a parameter "lead.phone" among all the 3 fields either one of them (Mobile_Phone_Number__c,a.Residence_Number__c,a.Work_Number__c) also along with that i also need to search an email "lead.email" with "Email__c" fields. Both the fields ("lead.phone" and "lead.email") have to be in OR conditions.

So far what I tried. but its not giving me Residence_Number__c and Work_Number__c number results

 if(lead.phone !=null){       
    qry += ' a.Mobile_Phone_Number__c= :leadPhone OR (a.Residence_Number__c= :leadPhone OR Work_Number__c= :leadPhone)'; 
    }
    if(lead.email !=null && lead.phone !=null){        
    qry += ' OR a.Email__c = :leadEmail';  
    }else if(lead.email!=null){
        qry += ' a.Email__c = :leadEmail';
    }           
    accList = database.query(qry); 
2

You are in the right direction and please note that this code will become much complex once there are more conditions added.

Gotchas:

  • If you are dealing with large data sets, make sure that all these fields in where clause are indexed else the query performance will be degraded and with so many OR conditions, if possible consider using an SOSL or two queries, one on phone and one on email
  • Sanitize your input before.
  • Consider using a library for dynamic SOQL. It may ease some of the tasks.

String leadPhone;
String leadEmail;
String qry = 'SELECT a.Id, a.Name,'
        + 'a.Email__c,a.Birth_Date__c,a.Mobile_Phone_Number__c,'
        + 'a.Residence_Number__c,a.Work_Number__c'
        + ' From Account a WHERE ';

if (String.isNotBlank(lead.phone)) {
    leadPhone = String.escapeSingleQuotes(lead.phone);
    qry += ' (a.Mobile_Phone_Number__c= :leadPhone OR a.Residence_Number__c= :leadPhone OR Work_Number__c= :leadPhone)';
}

if (String.isNotBlank(lead.email)) {
    if (String.isNotBlank(leadPhone)) {
        qry = qry + ' OR ';
    }
    leadEmail = String.escapeSingleQuotes(lead.email);
    qry += ' a.Email__c = :leadEmail';
}


List<Account> accList = Database.query(qry);
| improve this answer | |
1

The where clause, use the OR operator within the WHERE clause, but make sure the whole phrase is encapsulated by (). If you have other logic like AND, etc, not encapsulating the ORs with the parenthesis will give you issues.

So the query should look something like this:

SELECT Id, Name
  FROM Account
 WHERE (Email__c = :lead.email OR Mobile_Phone_Number__c = :leadPhone OR a.Residence_Number__c = :leadPhone OR a.Work_Number__c = :leadPhone)

I sometimes have issues with using :variable and I often just build the query by adding it to the string itself:

'WHERE (Email__c = \'' + lead.email + '\' OR Mobile_Phone_Number__c = \'' + leadPhone + '\' OR a.Residence_Number__c = \'' + leadPhone + '\' OR a.Work_Number__c = \'' + leadPhone + '\')';
| improve this answer | |

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.