3

I am trying to bulkify a trigger to match transactions with contacts by email address. There are many contacts with duplicate email addresses and getting rid of these duplicates is not an option. I am trying to write a SOQL query that returns only the most recently modified contact record for each email address in a set.

So far the only thing I've come up with is this monstrosity:

Set<String> emails = new Set<String>();

emails.add('email_1@example.com');
emails.add('email_2@example.com');

List<Contact> contacts = [SELECT Email, Id, LastModifiedDate FROM Contact WHERE Email IN :emails];
List<AggregateResult> agg = [SELECT Email, MAX(LastModifiedDate) FROM Contact WHERE Email IN :emails GROUP BY Email];

Map<String, DateTime> aggMap = new Map<String, DateTime>();

for (AggregateResult res : agg){
    aggMap.put(String.valueOf(res.get('Email')),DateTime.valueOf(res.get('expr0')));
}

List<Contact> keep = new List<Contact>();

for (Contact c : contacts){    
    if(c.LastModifiedDate == aggMap.get(c.Email)){
        keep.add(c);
    }       
}
System.debug(keep);

This gets me the desired result, but it seems extremely inefficient. Is there some way to achieve this same result with a single query?

0

Done in one query and one for loop:

Set<String> emails = new Set<String>();

emails.add('email_1@example.com');
emails.add('email_2@example.com');

Map<String,Contact> lastModifiedByEmail = new Map<String,Contact>();

for(Contact c : [SELECT Id, Email, LastModifiedDate FROM Contact WHERE Email IN :emails]){
    if(lastModifiedByEmail.get(c.Email) == null || lastModifiedByEmail.get(c.Email).LastModifiedDate < c.LastModifiedDate){
        lastModifiedByEmail.put(c.Email,c);
    }
}

System.debug(lastModifiedByEmail.values());

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.