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 a@bc.de 2012-11-16 1 good f@gh.ij 2012-08-20 2 good f@gh.ij 2012-11-11 3 good k@lm.no 2012-11-13 4 good k@lm.no 2012-09-14 5 good p@qr.st 2012-10-02 6 good p@qr.st 2012-10-02 7 good u@vw.xy 2012-07-30 8 good u@vw.xy 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 a@bc.de 2012-11-16 1 good f@gh.ij 2012-11-11 3 good k@lm.no 2012-11-13 4 good p@qr.st 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 k@lm.no 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 u@vw.xy