Finding most recent record when multiple records match criteria

Question

I have a custom sObject which has among others the following fields: email__c (email), some_date__c (date), external_id__c (integer) and criteria__c (string). There are multiple records for this sObject that have the same value for email__c.

If an email address returned in the results has multiple records we want to select the record with the most recent date. If there is a tie on the date then select the record with the greatest external id. However we only want to return the selected record if it matches the criteria.

Below is how I have been able to do this with Apex code but I am wondering if there is a way I can accomplish the same with a single SOQL query or with simpler code. Any suggestions?

public List<Project__c> getRecords(String criteria) {
    List<String> emails = new List<String>();
    Map<String, Project__c> emailProjectMap = new Map<String, Project__c>();
    List<Project__c> retVal = new List<Project__c>();

    for( Project__c project : [ Select email__c From Project__c 
                                Where criteria__c = :criteria ] ) { 
       emails.add(project.email__c);
    }
    for( Project__c project : [ Select email__c, some_date__c, external_id__c, criteria__c
                                From Project__c Where email__c in :emails 
                                Order by some_date__c, external_id__c ] ) {
       emailProjectMap.put(project.email__c, project);
    }
    for( Project__c project : emailProjectMap.values() ) {
        if( project.criteria__c == criteria ) {
            retVal.add(project);
        }
    }
    return retVal;
}

Here is some sample data demonstrating the different cases that occur:

email__c    some_date__c    external_id__c    criteria__c
[email protected]     2012-11-16             1          good
[email protected]     2012-08-20             2          good
[email protected]     2012-11-11             3          good
[email protected]     2012-11-13             4          good
[email protected]     2012-09-14             5          good
[email protected]     2012-10-02             6          good
[email protected]     2012-10-02             7          good
[email protected]     2012-07-30             8          good
[email protected]     2012-11-14             9          bad

If the criteria that we are searching for is "good" there should be 4 records returned. Below is the list of records that should be returned by the function.

email__c    some_date__c    external_id__c    criteria__c
[email protected]     2012-11-16             1          good 
[email protected]     2012-11-11             3          good
[email protected]     2012-11-13             4          good
[email protected]     2012-10-02             7          good

Note:

• The record with the max(some_date__c) for an email address may not have the max(external_id__c)
  • This is why the record with external_id__c = 4 is returned for the email [email protected] instead of external_id__c = 5
• A record for an email address may match the criteria but the record with the max(some_date__c) and if there is a tie the max(external_id__c) may not match the criteria
  • This is why there is no record returned for the email [email protected]

Answer 1

I have been able to make the function a bit more efficient and it definitely uses a lot less heap space. Thanks to @eyescream's updated solution for inspiration.

This version uses only 2 loops instead of three. In the first loop I find the "most recent" project that matches the criteria. Then in the second loop I query just for those records that don't match the criteria. If the record that does't match the criteria for an email is more recent then the record that did it is removed from the final result.

This still isn't a very efficient process but I think it might be the best that it gets. However that said if you think you have an improvement I would love to see it.

public List<Project__c> getRecords(String criteria) {
    Map<String, Project__c> emailProjectMap = new Map<String, Project__c>();

    /* ----- Get "Most Recent" Project that matches criteria ----- */
    for( Project__c project : [ Select email__c, some_date__c, external_id__c, criteria__c 
                                From Project__c 
                                Where criteria__c = :criteria 
                                Order by some_date__c, external_id__c ] ) { 
        emailProjectMap.put(project.email__c, project);
    }

    /* ----- Get Projects that match emails but don't match criteria ----- */
    for( Project__c project : [ Select email__c, some_date__c, external_id__c, criteria__c
                                From Project__c 
                                Where email__c in :emailProjectMap.keySet()
                                And criteria__c != :criteria ] ) {

        /* ----- If project that doesn't match criteria is "More Recent" than   ----- */
        /* ----- project that matches criteria remove matching project from map ----- */
        if( emailProjectMap.containsKey(project.pc_email__c) ) {
            Project__c criteriaProject = emailProjectMap.get(project.pc_email__c);
            if( criteriaProject.Delivered_Date__c < project.Delivered_Date__c || 
                ( matchProject.Delivered_Date__c == project.Delivered_Date__c && 
                  matchProject.project_id__c < project.project_id__c ) ) {
                        emailProjectMap.remove(project.pc_email__c);
    }

    return emailProjectMap.values();
}

Answer 2

I think you can skip the first loop and the last one, and just add the criteria to the middle loop. I also added an Order by on email__c, but it's not strictly necessary:

Map<String, Project__c> emailProjectMap = new Map<String, Project__c>();
List<Project__c> retVal = new List<Project__c>();

for( Project__c project : [ Select email__c, some_date__c, external_id__c, criteria__c
                            From Project__c Where email__c in :emails
                            where criteria__c = :criteria 
                            Order by email__c, some_date__c, external_id__c ] ) {
   emailProjectMap.put(project.email__c, project);
}
retVal = emailProjectMap.values();

This is only looking at criteria-matching records, so there's no need to go back and check them against it in the third loop. emailProjectMap will collect only the last Project record for each Email.

This is still fairly inefficient, in that you could loop through a lot of records that match criteria and email, putting them into the map and overwriting them. To be more efficient, you could use an aggregate query.

Answer 3

EDIT (see history of changes for original naive answer)

I'm afraid your solution is pretty good already given these requirements. Same as @JeremyNottingham I see no nice way to avoid 2 queries.

I've managed to rewrite it a bit but I'm not happy with losing the WHERE in the first query (it will explode the moment you'll have several thousands of criteria - project combinations).

Map<String, Date> goodEmails = new Map<String, Date>();     // Emails where we don't expect any traps and we'll just want to get max ext. id
Map<String, Date> suspectEmails = new Map<String,Date>();   // Emails where there might be something bad waiting to happen 
                                                            // (meaning that we don't want it returned if there's no good record later than > Date)

for(AggregateResult ar : [SELECT Email__c, Criteria__c, MAX(Some_Date__c)
    FROM Project__c
    GROUP BY Criteria__c, Email]){      // Would be easier with ORDER BY Criteria__c DESC here but I suspect you have more than just "good" and "bad" values?
    String e = String.valueOf(ar.get('Email'));
    Date d = Date.valueOf(ar.get('expr0'));

    if(String.valueOf(ar.get('Criteria__c')) == 'good'){
        goodEmails.put(e,d);
    } else {
        suspectEmails.put(e,d);
    }
}

if(!suspectEmails.isEmpty()){
    for(String e : suspectEmails.keyset()){
        if(goodEmails.containsKey(e) && goodEmails.get(e) < suspectEmails.get(e)){
            goodEmails.remove(e);
        }
    }
}

for(AggregateResult ar : [SELECT Email__c, Some_Date__c, MAX(External_id__c)
    FROM Project__c
    WHERE (Criteria__c = 'good' AND Email__c IN :goodEmails.keyset() AND Some_Date__c IN :goodEmails.values())
    GROUP BY Email__c, Some_Date__c]){
    System.debug(ar);
}

Answer 4

As far as I can see, this would do the job:

[SELECT Id, Email__c FROM Project__c WHERE Criteria__c = :criteria ORDER BY Some_Date__c, External_Id__c DESC][0]

Therefore your function would be:

public Project__c getRecord(String criteria) 
{
    return [SELECT Id, Email__c FROM Project__c WHERE Criteria__c = :criteria ORDER BY Some_Date__c, External_Id__c DESC][0];
}

You can add try catch block to make sure you don't get exceptions if there no such records btw...