[SalesForce] Get Record Id of Max(Date) record from Aggregate Result SOQL query (most recent campaign membership by campaign enddate)

First time using aggregate functions in SOQL in Apex, and looking for a little bit of help.

I'm trying to pull the "last" campaign membership off a contact in apex so that I can update their status, but last as in Max(Campaign.EndDate) not Max(CreatedDate) from CampaignMember.

My goal is to get a set of campaign memberships that are of the most recent "xyz" campaign, so my query looks like:

List <AggregateResult> mostRecentCMs = [SELECT ContactId, MAX(Id) id, MAX(Campaign.EndDate) FROM CampaignMember
  WHERE ContactId = :contactList AND Campaign.Type = 'XYZ' AND Status = 'Responded' GROUP BY ContactId];

but I don't know 1. if that query is right and 2. how to get that list into a set of CampaignMember Ids. My best guess at the latter based off Jeff Douglas' post is as follows, but I don't really understand the cmSet.add((ID)results.get('id')) part. What I'm particularly concerned with is that the Max(Id) is not the same thing as the Id of the CampaignMember record with the Max(Campaign.EndDate).

for(AggregateResult ar : mostrecentCMs){
  System.debug(ar.get('ContactId') + '-' + ar.get('id'));
}

for (AggregateResult results : mostrecentCMs){
  mqlsToSAL.add((ID)results.get('id'))
}
for(CampaignMember sal : mostRecentCMs){
  sal.Status = 'SAL';
  toUpdate.add(sal);
}

I got worried based off this post that I should actually be using a subquery instead of an aggregate function, but I don't think that make sense when Campaign isn't a child of CampaignMember.

Can anyone help get me past this roadblock and/or course correct me?

Edit:
Based off Adrian's idea, maybe this should work off the Campaign. Does the below look correct?

List <Campaign> c = [SELECT Id, EndDate,
    (SELECT Id FROM CampaignMembers WHERE Status = 'Responded')
FROM Campaign WHERE Type = 'XYZ' AND Id IN
    (SELECT CampaignId FROM CampaignMember WHERE ContactId = :contactList)
ORDER BY EndDate desc limit 1];

Set <CampaignMember> mostRecentCMs = new Set <CampaignMember>;
mostRecentCMs.add(c.CampaignMembers);

Set <Id> cmsToUpdate = new Set <Id>;
for (CampaignMember cm : mostRecentCMs){
    cmsToUpdate.add(cm.Id);
}

Best Answer

I think you would have an easier time querying Campaign directly here:

SELECT EndDate,
    (
        SELECT ContactId FROM CampaignMembers
        WHERE Status = 'Responded'
        ORDER BY CreatedDate DESC LIMIT 1
    )
FROM Campaign WHERE Type = 'XYZ' AND Id IN (
    SELECT CampaignId FROM CampaignMember WHERE Status = 'Responded'
)

There are a few things going on here:

  • You can use a Left Outer Join to get the child CampaignMember records within the same query.
    • These query rows count against your per transaction limit.
    • The query itself counts against a different limit than parent queries, and is somewhat misleadingly retrieved using Limits.getAggregateQueries()
  • You can use a `Left Inner Join to check that there actually are matching child records.
    • These query rows do not count against your limits consumption.
    • This sub-query does not count against limit consumption either.
Related Topic