[SalesForce] Multiple WHERE IN conditions in SOQL query

Can I use the below syntax in SOQL query.

set<String> firstName = new set<String>();
set<String> lastName =  new set<String>();
set<String> ssnNumber = new set<String>();

[select firstName,lastName,ssnNumber from Nominee where firstName IN :firstName AND
                                                        lastName  IN : lastName AND
                                                        ssnNumber IN  :ssnNumber]

can i use the above format of SOQL query with multiple WHERE in conditions.

Best Answer

For this type of question, a pretty simple "can I do X?", the fastest way to get an answer is usually to just go and try to do it yourself through anonymous apex.

The short answer here is yes, you can do this, and in exactly the way that you laid out in your example.

The longer answer is yes, and you need to keep in mind that you're going to get the cartesian product of your filters.

I.e. if you have {'John', 'Vijay', 'Andrei'} in your firstName set
and {'123-45-6789' ,'123-45-6790'} in your ssnNumber set
you'll get results for:

John + 123-45-6789
John + 123-45-6790
Vijay + 123-45-6789
Vijay + 123-45-6790
Andrei + 123-45-6789
Andrei + 123-45-6790

if they exist.

SOQL is unable to do things like "find records that match index I in all of these collections". If that's what you're looking to do, then you'll need to do some extra work (like further filtering the query results in Apex, or creating a formula field on your object to act as a "composite key" of sorts)

Related Solutions

[SalesForce] Can a SOQL query selecting all columns be generated at runtime

Absolutely there is. Use the following syntax:

// write your query string exactly as in a SOQL statement minus the square-brackets
string queryString = 'SELECT id,Name,OpportunityId FROM Account WHERE OpportunityId!=null LIMIT 5';

// then use database.query() method.  
// Note its return value is a list<sObject> though you can cast it appropriately

list<sObject> sAccountList=database.query(queryString);
list<Account> accountList=(list<Account>)database.query(queryString);

You can also reference instance variables, but not any variable of an instance

set<id> myIdSet;

string queryString = 'SELECT id,Name FROM Account WHERE id IN:myIdSet';
list<Account> accountList = (list<Account>)database.query(queryString);

// XXXXXXXX   The following WONT work    X_X

map<id,Account> accountMap = new map<id,Account>(accountList);

string anotherQueryString = 'SELECT id,Name FROM Account WHERE id IN:accountMap.keyset()';  // X_X WILL NOT WORK 

list<Account> emptyAccountList = (list<Account>)database.query(anotherQueryString);
system.assert(emptyAccountList.isEmpty());

And since you mentioned there being no equivalant to SELECT * FROM ... in SOQL, here's some code I've written to get around that. You'll need to dig into the code to see how it all works. It's very handy when starting a project.

    //=================   QUERY UTIL METHODS   =================================

//#################   Format Fields For Query   ###########################################
public static string FormatFieldsForQuery(sObject M, string prefix){
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    for(string f : M.getSObjectType().getDescribe().fields.getMap().keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');
}//END FormatFieldsForQuery(sObject M, string prefix)

public static string FormatFieldsForQuery(sObject M){
    return FormatFieldsForQuery(M,'');
}//END FormatFieldsForQuery(sObject M)

public static string FormatFieldsForQuery(string objectName){
    return FormatFieldsForQuery(objectName,'');
}//END FormatFieldsForQuery(string objectName)

public static string FormatFieldsForQuery(string objectName,string prefix){
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    sObjectType objectType=Schema.getGlobalDescribe().get(objectName);
    if(objectType==null)
        return fields;
    for(string f :objectType.getDescribe().fields.getMap().keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');  
}//END FormatFieldsForQuery(string objectName,string prefix)

public static string FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix){ 
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    for(string f : m.keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');
}//END FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix)

public static string AllFieldsQuery(string objectName){
    return 'SELECT '+FormatFieldsForQuery(objectName)+' FROM '+objectName+' ';
}//END AllFieldsQuery

public static string AllFieldsQuery(sObject s){
    return 'SELECT '+FormatFieldsForQuery(s)+' FROM '+s.getSObjectType().getDescribe().getName()+' ';
}//END AllFieldsQuery

Test Code:

//#################   Format Fields For Query   ###########################################
@isTest private static void testFormatFieldsForQuery(){
    // Test order (depending upon input:
    //
    // #1 (sObject M, string prefix)
    // #2 (sObject M)
    // #3 (string objectName)
    // #4 (string objectName,string prefix)
    // #5 (map<String,Schema.SObjectField> m,string prefix)
    // #6 AllFieldsQuery(string objectName)   <---- different method name ----
    ///////////////////////////////////////////////////////////////////////////
    test.startTest();
    //test Account Standard Fields
    Account a=new Account();
    assertFFFQ(Portal.FormatFieldsForQuery(a,''),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery(a),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery('Account'),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery('Account',''),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Account.fields.getMap(),''),fffqTestObj.Account);
    assertFFFQ(Portal.AllFieldsQuery('Account'),fffqTestObj.Account);

    //test Contact Standard Fields
    Contact c=new contact();
    assertFFFQ(Portal.FormatFieldsForQuery(c,''),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery(c),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery('Contact'),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery('Contact',''),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Contact.fields.getMap(),''),fffqTestObj.Contact);
    assertFFFQ(Portal.AllFieldsQuery('Contact'),fffqTestObj.Contact);

    // bad objectName for 
    // #3 (string objectName)
    // #4 (string objectName,string prefix)
    system.assert(string.isBlank(Portal.FormatFieldsForQuery('This shouldn\'t work!!!')));

}//END testFormatFieldsForQuery2

public enum fffqTestObj {Account,Contact}

public static void assertFFFQ(string fieldString,fffqTestObj objName){
    set<string> fields=new set<string>(fieldString.removeStart('SELECT ').substringBefore(' FROM').split(','));

    if(objName==fffqTestObj.Account){
        system.assert(fields.contains('name'));
        system.assert(fields.contains('accountnumber'));
        system.assert(fields.contains('ownerid'));
        system.assert(fields.contains('site'));
        system.assert(fields.contains('accountsource'));
        system.assert(fields.contains('annualrevenue'));
        system.assert(fields.contains('createdbyid'));
        system.assert(fields.contains('jigsaw'));
        system.assert(fields.contains('description'));
        system.assert(fields.contains('numberofemployees'));
        system.assert(fields.contains('fax'));
        system.assert(fields.contains('industry'));
        system.assert(fields.contains('lastmodifiedbyid'));
        system.assert(fields.contains('ownership'));
        system.assert(fields.contains('parentid'));
        system.assert(fields.contains('phone'));
        system.assert(fields.contains('rating'));
        system.assert(fields.contains('sic'));
        system.assert(fields.contains('sicdesc'));
        system.assert(fields.contains('tickersymbol'));
        system.assert(fields.contains('type'));
        system.assert(fields.contains('website'));
    }
    else if(objName==fffqTestObj.Contact){
        system.assert(fields.contains('accountid'));
        system.assert(fields.contains('assistantname'));
        system.assert(fields.contains('assistantphone'));
        system.assert(fields.contains('birthdate'));
        system.assert(fields.contains('ownerid'));
        system.assert(fields.contains('createdbyid'));
        system.assert(fields.contains('jigsaw'));
        system.assert(fields.contains('department'));
        system.assert(fields.contains('description'));
        system.assert(fields.contains('donotcall'));
        system.assert(fields.contains('email'));
        system.assert(fields.contains('hasoptedoutofemail'));
        system.assert(fields.contains('fax'));
        system.assert(fields.contains('hasoptedoutoffax'));
        system.assert(fields.contains('homephone'));
        system.assert(fields.contains('lastmodifiedbyid'));
        system.assert(fields.contains('lastcurequestdate'));
        system.assert(fields.contains('lastcuupdatedate'));
        system.assert(fields.contains('leadsource'));
        system.assert(fields.contains('mobilephone'));
        system.assert(fields.contains('otherphone'));
        system.assert(fields.contains('phone'));
        system.assert(fields.contains('reportstoid'));
        system.assert(fields.contains('title'));  
    }
}//END assertFFFQ

[SalesForce] How to query for records that have specific values on multiple fields

Keith C's approach would work, but like he mentioned, WHERE clause size is a concern.

Based on the documentation on SOQL and SOSL limits, the WHERE clause of a SOQL query cannot exceed 4000 characters.

If your first name, last name, and company name are all 5 characters long (on average), each triplet would consume 81 characters of the 4000 limit. Having just 50 filter values would put you over 4000 characters.

Getting back to why you need to loop over the query results, that's because when you use multiple <field> IN :<variable> filters, SOQL gives you the cartesian product of those filters (it can't do anything other than give you the cartesian product). That means that your query will always return the following records (if they exist)

(format is First Name + Last Name + Company)
A + K + X
A + K + Y
A + K + Z
A + L + X
A + L + Y
...
C + M + X
C + M + Y
C + M + Z

(A total of 27 possible records).

It is possible to avoid getting the cartesian product, but it requires a little work. First, you'll want to create a formula field on Contact that concatenates the FirstName, LastName, and Company fields. This is the closest thing to a 'composite key' that Apex/SOQL can currently support (but this field will not be indexed by default).

Next we need to change your sets of values to filter on into lists instead.

We need lists because of this next step

List<String> compositeKeys = new List<String>();
// Assuming that first name, last name, and company are all the same size
for(Integer i = 0; i < firstNameList.size(); i++){
    // This should basically mirror the formula field's formula
    compositeKeys.add(firstNameList[i] + lastNameList[i] + companyList[i]);
}

Your query would then become

// It's still a good idea to include filters on FirstName and LastName (though 
//   you may need to concatenate those as well) because the Name field on Contact
//   is indexed (and you may run into selectivity issues without it)
Contact[] contactList = [
    SELECT Id, FirstName, LastName, Company 
    FROM Contact 
    WHERE FirstName IN :firstNameSet AND LastName IN :lastNameSet AND Company IN :companySet 
        AND compositeKeyFormula IN :compositeKeys
];

That should allow you to avoid the loop over the query results to filter out the stuff from the cartesian product that you don't want.

Best Answer

Related Solutions

[SalesForce] Can a SOQL query selecting all columns be generated at runtime

[SalesForce] How to query for records that have specific values on multiple fields

Related Topic