0

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);
}
2

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.
2
  • Hi Adrian, you might be right but your above example doesn't work for what I need, so I just want to make sure changing it doesn't change its eligibility as an answer. 1. I need the Id of the CampaignMembers, not the ContactId (this is a trigger on Contacts, so I have a set of Contact Ids as the CampaignMember where clause as a bind variable). 2 The date filter needs to be on the Campaign, not the CampaignMember (Order by Campaign EndDate desc Limit 1, to allow for late adds on campaigns). 3. I need to add the CampaignMember Ids to a set. Do you think my edits to question would work? – mlpSFadmin Apr 24 '17 at 21:30
  • Ah poop I just wrote up the edits but I forgot - I can't do limit 1 on the campaign because that will only return 1 campaign, when there could be multiple here at play. – mlpSFadmin Apr 24 '17 at 21:36
1

I ended up just doing this as two queries, one off the campaign earlier in the trigger:

  List <Campaign> activeCampaign = [
  SELECT Id, Name, Type, StartDate, EndDate FROM Campaign
  WHERE Type = 'xyz' ORDER BY EndDate desc
  LIMIT 1];

and then using that query's results in a bind variable later:

  List <CampaignMember> mostRecentCMs = [SELECT Id, CampaignId, ContactId, Status FROM CampaignMember
  WHERE ContactId = :contactList AND CampaignId = :activeCampaign[0].Id];

Works well enough. I bet there's still some way to consolidate this into one query, but this has been working for us in the meantime!

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.