How to query Objects, where all conditions matched at teh same time

Question

I have to write a query where all conditions should match at the same time.

Account has 3 custom fields: Country__c, State__c, and Customer__c (look up to Customer_Account__c object).

The Customer_Account__c object also has a 2 custom fields (Country__c and State__c).

Whenever I create an Account record, it will first check if any Customer_Account__c record exists in our database based on country and state. If yes, then map that record (Customer_Account__c) to Account__c.Customer__c . If not, create a new one.

I can deal with mapping Customer_Account__c to Account. But I am having difficulty with how to query Customer_Account__c record with matching Account field values.

Example:

If an Account has Country__c = 'Australia' and State__c = 'Brisbane', then it will query Customer_Account__c records where Country__c = 'Australia' and State__c = 'Brisbane'.

Both conditions should match together. And of course we are bulkifying the trigger.

Answer 1

You sort of have two questions, how to match multiple criteria and how to match against a collection.

The first is quite straightforward, use the AND clause:

SELECT Id FROM Customer_Account__c
WHERE Country__c = 'Australia'
AND State__c = 'Brisbane'

The second is also fairly simple. Use the IN clause. I would recommend using a Set and also make sure it does not contain null so as to avoid a TableScan. You would also get the aforementioned TableScan if the Set is empty, so don't run the query in that case.

Set<String> countries = new Set<String>();
Set<String> states = new Set<String>();
for (Account account : accounts)
{
    if (account.Country__c != null) countries.add(account.Country__c);
    if (account.State__c != null) states.add(account.State__c);
}
if (countries.isEmpty() || states.isEmpty() return new List<Account>();
return [
    SELECT Id FROM Customer_Account__c
    WHERE Country__c IN :countries
    AND State__c IN :states
]