For Loop - Query Account Object from List of Contacts

Question

I am trying to extract the email domain from each contact record from the email address field and use it to query for matching values on the account record.

How do I loop over a list of contact records and query a field on the account record while grabbing just the domain part of the email address from the email field on each given contact in the loop? This is the code I have so far:

for(Contact con : contacts) {
  if( con.email != null ) {
     contactList.add( con );
  }
}

for(Account acc : [SELECT Id, Name FROM Account WHERE account_email__c = 
 **contactemaildomain**]  
) {
  .. code ..
}

I am confused on how to use the values from my contactList in my query to the account.

Answer 1

No matter what you do, one major problem you're going to encounter is that your trigger will eventually start failing because of non-selective query issues. So, on the account itself, you need to create a field called "Email Domain", and copy the domain part of the email to this field.

It must either be a text field, or an indexed formula (requires Customer Support to enable), or you'll still have query issues later. Keep in mind you'll want to have a trigger/process builder/workflow rule on the account to populate the field, and you'll want to touch each record once to make sure the value is correctly updated. This is a one time process.

---

Presuming you have an email domain field on the account, your code will be incredibly simple, using the standard aggregate-query-update pattern:

// Aggregate
Set<String> domains = new Set<String>();
for(Contact record: contacts) {
  if(record.email != null) {
    domains.add(record.email.split('@')[1]);
  }
}
// Query
Map<String, Id> accountsByDomain = new Map<String, Id>();
for(Account record: [select Email_Domain__c from account where Email_Domain__c = :domains]) {
  accountsByDomain.put(record.Email_Domain__c, record.Id);
}
// Update
for(Contact record: contacts) {
  if(record.email != null) {
    Id accountId = accountsByDomain.get(record.email.split('@')[1]);
    // ... If accountId is null, no match was found
  }
}