+ Start a Discussion
lovetolearnlovetolearn 

Help with Avoiding SOQL Query Limit

Hi, 

 

I have an objected called Helper__c. On my Case, there is a lookup field to this object. My goal is to update the three custom fields on the Helper__c object whenever a Helper record is selected. The custom fields are Total Number of Cases, Date of Last Case and Last Case Number. I wrote a trigger for this, but my the trigger is bulk-safe and I always get SOQL Query limit errors during mass updates. Please help me correect the following code:

 

Trigger:

 

trigger helperCases on Case (after insert, after update, after delete) {
    
    if(trigger.isUpdate || trigger.isInsert){
        for(Case c: trigger.new){
            if(c.helper__c == null){
                
            }else if(c.helper__c != null){
                testUpdate.testHelperUpdate(c.Helper__c);
            }
        }
    }if(trigger.isUpdate){
    	for(Case c : trigger.old){
            if(c.Helper__c == null){
    			
    	    }else if(c.Helper__c !=null){
    		testUpdate.testHelperUpdate(c.Helper__c);
    	    }
    	}
    }if(trigger.isDelete){
        for(Case c: trigger.old){
            if(c.Helper__c == null){
                
            }else if(c.Helper__c != null){
                testUpdate.testHelperUpdate(c.Helper__c);
            }
        }   
    }
}

 

Apex Class:

 

public class testUpdate{
     public static void testHelper(Id helperId){
        Double numberofCases;
        String cNum;
        LIST<CASE> c = [SELECT ID, CaseNumber, CreatedDate FROM CASE WHERE Helper__c =: helperID ORDER BY CaseNumber DESC];
        numberofCases = c.size();
        Helper__c helper = [SELECT ID,  all_cases__c, Last_case__c, Last_case_date__c FROM Helper__c WHERE ID =: HelperID];
        helper.all_cases__c = numberofCases;
        if(c.size()>0){
            cNum = c[0].ID;
            Helper.last_case__c = cNum;
            Helper.Last_case_date__c = c[0].CreatedDate;
        }else{
            Helper.last_case__c = null;
            Helper.Last_case_date__c = null;
        }
        update helper;

     }
}

 Thank you.

PrachiPrachi

Hi,

 

In your trigger, the apex class is being called for each Case record where helper__c is not null. This is causing failure during mass updates.

I suggest that you add all the helper Ids which satisfy your criteria in a list and then pass the list in the class.

You can then query the case object in you Apex class.

Something like this might help:

 

List<Id> helperList = new List<Id>();
for(Case c: trigger.new){
if(c.helper__c == null){
}else if(c.helper__c != null){
        helperList.add(c.Helper__c);
}
}
if(helperList.size()>0)
       testUpdate.testHelperUpdate(helperList);

 

You will also have to alter the method in your class accordingly.

lovetolearnlovetolearn

I thought about that, but if I do that and pass in a list. Would I still need to run SOQL queries to query for the number of Cases per helper and the last case and the date of the case for each helper?

PrachiPrachi

Yes..you will have to query the helper object for any helper related details. You can use the count() function in your SOQL query to get the number of cases for each helper.


lovetolearn wrote:

I thought about that, but if I do that and pass in a list. Would I still need to run SOQL queries to query for the number of Cases per helper and the last case and the date of the case for each helper?