Bulkify SOQL with multiple date comparisons

Question

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?

Answer 1

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 :)