Apex Trigger -- Using SOQL Query With Like

Question

I am trying to write a trigger that matches a domain on a contact (extracted from an email address) with a domain on an account. If there's a match, then update the account lookup field on the contact with the appropriate name. I used this article as a starting point: Assign Account to Contact based on email domain.

In my case, I want to take this a step further by using a domain field on the account that contains multiple domains separated by a space (e.g. Acme Corp's email domains could be "acme.com acmecorp.com acme123.com"). I have been playing around with the SOQL query to see if I could return a match if the text field contained one of the domains, instead of requiring an exact match.

I am receiving this error for the piece of code below:

Compile Error: Unrecognized symbol '\', which is not a valid Apex identifier. at line 6 column 75

How can I revise the query to compile and run?

trigger AssociateContact on Contact (before Update) {
    Map<String, Id> domains = new Map<String, Id>();
    for(Contact record: Trigger.new) {
        domains.put(record.Domain__c, null);
    }
    for(Account record: [SELECT Domains__c FROM Account WHERE Domains__c LIKE 
\'%' + domains.keySet() + '%\'']) {
        domains.put(record.Domains__c, record.Id);
    }
    for(Contact record: Trigger.new) 
    {
        if(domains.get(record.Domain__c) != null) 
        {
            record.AccountId = domains.get(record.Domain__c);
        }
    }
}

Answer 1

You need to use the : (bind) operator in order to bind to the variable when using static SOQL. However, you also need to create a list of values that are already appropriately made. This is because simply doing % + keySet + % won't compile, and even if you did, it would look like this: %domain1,domain2,domain3%, which simply wouldn't work. Here's a modified version:

Map<String, Id> domains = new Map<String, Id>();
Set<String> wildcardDomains = new Set<String>();
for(Contact record: Trigger.new) {
  domains.put(record.Domain__c, null);
  wildcardDomains.add('%'+record.Domain__c+'%');
}
for(Account record: [SELECT Domains__c FROM Account WHERE Domains__c LIKE 
:wildcardDomains]) {
 // Rest of code...

Since you're doing wildcards, you're going to also have problems trying to match the values directly, so you're going to think about a way to match the domains correctly. I suspect you're going to practically need to use a nested for loop, which may have performance problems when used in bulk.