2

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
  • I confess I don't understand the "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" - can you somehow rephrase it? I don't understand why you're excluding record with ext. id 8. There's one with later date but it doesn't meet the criteria, right? You're sure your original algorithm excludes it? – eyescream Nov 16 '12 at 19:40
  • Let me walk through my original algorithm and maybe that will help. The first loop in my algorithm would get the email address u@vw.xy and add it to the emails list because the record with ext. id = 8 matches the criteria. Then the query in the second loop would return both records with ext. id = 8 & 9 because they both have the email u@vw.xy. Due to the order by record 8 would be before record 9 so what ends up in the emailProjectMap would be record 9. Finally in the third loop record 9 would not get added to the retVal list because the criteria doesn't match. Does that help? – sfelf Nov 16 '12 at 20:07
  • Yeah, sorry, I'm blind. I've missed the ORDER BY. – eyescream Nov 16 '12 at 20:08
  • It sounds like you're saying, "For this list of email addresses, return the latest Project if it matches my criteria, and return nothing at all if the latest Project doesn't match." Does that sound right? – Jeremy Nottingham Nov 16 '12 at 21:52
  • @JeremyNottingham That sounds about right. Where latest Project is described as most recent some_date__c and if there is a tie the greatest external_id__c. – sfelf Nov 16 '12 at 22:01
2

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();
}
|improve this answer|||||
  • Looks really nice now :) – eyescream Nov 17 '12 at 8:52
0

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.

|improve this answer|||||
  • Thanks so much for your answer. This seems to cover all the cases except the one where an email address has a record that matches the criteria but the record for the email address with the max(some_date__c) and if there is a tie on that the max(external_id__c) might not match the criteria. I have added sample data to my question to better illustrate the problem I am trying to solve. – sfelf Nov 16 '12 at 18:18
  • I see. I started to fix my answer to work for you, and realized I was going to end up with exactly what you had already devised. If you have to go from project criteria to email addresses back to projects, you'll need at least two queries. – Jeremy Nottingham Nov 16 '12 at 22:04
0

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);
}
|improve this answer|||||
  • Thanks so much for your answer. This solution seems to assume the record with the max(LastModifiedDate) also has the max(Phone). Unfortunately that is not the case with the data set I am working with. I have added sample data to my question to better illustrate the problem I am trying to solve. – sfelf Nov 16 '12 at 18:21
  • Thanks so much for your updated solution! Your idea to aggregate in the first query inspired me. I have posted the best solution I have come up with so far. – sfelf Nov 17 '12 at 2:20
-1

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...

|improve this answer|||||
  • I don't think this will work. There are many records that match the criteria and not necessarily all will have the same email address. Additionally even though a record matches the criteria another record may exist with the same email address that has a some_date__c value greater than the matching record that doesn't match the criteria. – sfelf Nov 16 '12 at 2:47
  • My query will return the only the records that match the criteria, then it will order them by some date (most recent first), and then again by the external ID (most recent first) and will return the most recent one. Isn't that what you are after? – Boris Bachovski Nov 16 '12 at 3:00
  • With [0] it's like you'd apply LIMIT 1 there... will work for only 1 project, won't be any good when you want more – eyescream Nov 16 '12 at 7:18
  • Thank you so much for your answer. I have added some sample data and the results I would expect from this function. I hope this helps in better illustrating the problem I am trying to solve. – sfelf Nov 16 '12 at 18:24

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.