2

I am just starting out with apex and learning about bulkifying queries but I bumped up across a query that I am not sure how to bulkify.

What I am trying to do is return a Count of the number of leads/contacts who are members of a campaign and who's SQL dates are greater than the campaigns start dates.

The relevant fields are:

Campaign

  • Campaign.Name
  • Campaign.StartDate

CampaignMember

  • CampaignMember.Id
  • CampaignMember.HasResponded

Lead

  • Lead.SQL_Date__c

Contact

  • Contact.SQL_Date__c

I have it working as a trigger, but I want to figure out how to optimize this.

trigger UpdateInfluences on Campaign (before update) {
    for (Campaign currentCampaign : Trigger.new) {
        String campaignName = currentCampaign.Name;
        DateTime startDate = currentCampaign.StartDate;
        List<AggregateResult> queryCount = [SELECT Count(CampaignMember.Id) total FROM CampaignMember WHERE CampaignMember.HasResponded = true AND Campaign.Name = :campaignName AND Lead.isMQL__c = true AND (Lead.MQL_Date__c >= :startDate OR Contact.MQL_Date__c >= :startDate)];
        currentCampaign.Influenced_MQLs__c =  (Decimal)queryCount[0].get('total');
    } 
}

If this was SQL I could easily do something like:

SELECT Campaign.Name, Count(CampaignMember.Id) total 
    FROM CampaignMember 
    WHERE CampaignMember.HasResponded = true 
        AND Lead.isMQL__c = true 
        AND (Lead.MQL_Date__c >= Campaign.StartDate OR Contact.MQL_Date__c >= Campaign.StartDate)
    GROUP BY Campaign.Name

And just get them all in one go, but as far as I can tell, SOQL doesn't allow comparing a stored date to another stored date. So it seems I have to cycle through each Campaign to get the StartDate and then do the comparison.

I thought about just pulling all the campaignMembers for the campaigns in the trigger and then doing the comparison manually in code, but that would hit the 50k SOQL result limit instead and I think that would be pretty slow.

Any ideas?


I worked the problem a little more and I think I came to a solution that should work for bulk creation. Not sure if it is the best solution though.

trigger UpdateInfluences on Campaign (before update) {
        Set<String> campaignNames = New Set<String>();
        Map<String,Datetime> dateTimeMap = New Map<String,Datetime>();
        Map<String,Integer> resultCounts = New Map<String,Integer>();

        for (Campaign currentCampaign : Trigger.new) {
            campaignNames.add(currentCampaign.Name);
            dateTimeMap.put(currentCampaign.Name, currentCampaign.StartDate);
            resultCounts.put(currentCampaign.Name, 0);
        }

            List<CampaignMember> leadResults = [SELECT CampaignMember.Id, Lead.MQL_Date__c, Contact.MQL_Date__c, Campaign.Name FROM CampaignMember WHERE CampaignMember.HasResponded = true AND Campaign.Name IN :campaignNames]; 

    for (CampaignMember c : leadResults) {
        if (c.Lead.MQL_Date__c>= dateTimeMap.get(c.Campaign.Name) || c.Contact.MQL_Date__c>= dateTimeMap.get(c.Campaign.Name))
            resultCounts.put(c.Campaign.Name,resultCounts.get(c.Campaign.Name)+1);
     }

    for (Campaign currentCampaign : Trigger.new) {
        currentCampaign.Influenced_MQLs__c = (Decimal)resultCounts.get(currentCampaign.Name);
    }    
}

Instead of counting using SOQL I am pulling all records and then counting them manually. Is there a better way?

0
  1. You should not run SOQL query in for loop

You can do something like following to bulkify :

trigger UpdateInfluences on Campaign (before update) {
    Set<String> campaignNameSet = New Set<String>();
    Set<Datetime> dateTimeSet = New Set<Datetime>();
    for (Campaign currentCampaign : Trigger.new) {
        campaignNameSet.add(currentCampaign.Name);
        String campaignName = currentCampaign.Name;
        dateTimeSet.add(currentCampaign.StartDate);
    }
    List<AggregateResult> queryCount = [SELECT Count(CampaignMember.Id) total 
                                                FROM CampaignMember WHERE CampaignMember.HasResponded = true 
                                                    AND Campaign.Name IN : campaignNameSet
                                                    AND (Lead.CreatedDate >= :dateTimeSet 
                                                            OR Contact.CreatedDate >= :dateTimeSet)]; 
}

Hope this will help you :)

  • Thanks for the answer. I wasn't aware that you could use sets in SOQL. Aren't sets unordered? Is it guaranteed that the matching name will be run through the query at the same time as the matching date? Also, why do you initialize the Sets twice? Once before and once after the loop? – asielen Jan 7 '16 at 17:45
  • I have changed the code and removed the wrong initialization. where clause are to check against the sets you provide. If you want to get data in some order you can use ORDER BY clause. Please mark as answer, if it helps you so that other can get help. – ashishcloud Jan 7 '16 at 17:54
  • Thanks, I guess the confusion I am having isn't about a particular order but rather making sure the appropriate Date is matched with the appropriate CampaignName. Without a better understanding of SOQL. I would think that Campaign.Name IN :campaignNameSet would look for every Campaign with a name that is in the CampaignNameSet. That part makes sense. What I don't understand is how the Date comparison works against a Set. Does it match Campaign to date and do it that way? Or does it check if Lead.CreatedDate is >= ANY date in the dateTimeSet? – asielen Jan 7 '16 at 18:12

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.