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

Question

Let's say that I have to query Contact records with:

FirstName as A, LastName as B, Company as C, or

FirstName as K, LastName as L, Company as M, or

FirstName as X, LastName as Y, Company as Z.

How I satisfy this requirement is that, I create three Set collections for each column values:

Set<String> firstNameSet = new Set<String>{'A', 'K', 'X'};
Set<String> lastNameSet = new Set<String>{'B', 'L', 'Y'};
Set<String> companySet = new Set<String>{'C', 'M', 'Z'};

Then querying all possible combinations in Contact object:

Contact[] contactList = [SELECT Id, FirstName, LastName, Company FROM Contact WHERE FirstName IN : firstNameSet AND LastName IN : lastNameSet AND Company IN : companySet];

Lastly, I (usually) run a nested loop and extract the matching Contact records from contactList collection (showing only X, Y, Z requirement):

Contact[] contactListMatching = new List<Contact>();
for (Contact c : contactList) {
        if (c.FirstName='X' AND c.LastName='Y' AND c.Company='Z')
                contactListMatching.add(c);
}

I am wondering if there's a more optimal way to do this query. This approach increases CPU time when the dataset is big. I would like to transfer some of processing time to the SOQL operation, if possible.

Answer 1

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.

Answer 2

I would use dynamic SOQL for this appending a where clause term of this form for each triplet:

or (FirstName = 'A' and LastName = 'B' and Company = 'C')

Make sure to avoid SOQL injection risk and also be aware of these SOQL length Limits.