0

When i run the test code, it throws me message "System.LimitException: Too many SOQL queries: 101". How do i avoid using too many SOQL in this code.

    trigger GetObjLabel on Task (before insert, before update) {
    for(Task t : trigger.new){
     if(t.whatID != null){
      List<HOA__c> HOAList= [select id,name from HOA__c where id =: t.whatID];
      LIST<Patient_Event__c> SafetyEventList=[select id, name from Patient_Event__c where id=:t.whatID];
      List<Review__c> SEreviewList=[Select id, Name from Review__c where id=:t.whatId];
      List<Patient_Satisfaction__c> PRList=[Select id, Name from Patient_Satisfaction__c where 
      id=:t.whatId];
      List<Root_Cause_Analysis__c> RCAList=[Select id, Name from Root_Cause_Analysis__c where 
      id=:t.whatId];
        if(HOAList.size() > 0){
        for(HOA__c o : HOAList){
                t.Object_Label__c= 'HOA' + '('+o.Name+ ')'; //This will give output as HOA(<HOA. name>)
             }
          }
          else if(SafetyEventList.size() > 0){
             for(Patient_Event__c a : SafetyEventList){
                t.Object_Label__c= 'Safety Event' + '('+a.Name+ ')';  
             }
          }
           else if(SEreviewList.size() > 0){
             for(Review__c r : SEreviewList){
                t.Object_Label__c= 'Safety Event Review' + '('+r.Name+ ')'; 
             }
          }
              else if(PRList.size() > 0){
             for(Patient_Satisfaction__c r : PRList){
                t.Object_Label__c= 'Patient Relations' + '('+r.Name+ ')'; 
             }
          }
              else if(RCAList.size() > 0){
             for(Root_Cause_Analysis__c r : RCAList){
                t.Object_Label__c= 'RCA' + '('+r.Name+ ')';
             }
          }
          else{
               t.Object_Label__c= null;
          }
      }
   }
        
}
  • 1
    welcome to SFSE; you have soql inside for loop . Please read up on apex bulkification patterns – cropredy Nov 4 '20 at 21:43
  • 1
    This is a common enough question that the community has put together a canonical question and answer for this: salesforce.stackexchange.com/questions/321846/… – Derek F Nov 4 '20 at 21:44
1

You would want to use the Aggregate-Query-Update pattern. Here's one possible revision that should do what you want:

trigger GetObjLabel on Task (before insert, before update) {
    Map<Id, sObject> whatIds = new Map<Id, sObject>();
    Set<sObjectType> types = new Set<sObjectType>();
    for(Task record: Trigger.new) {
        whatIds.put(record.WhatId, null);
        if(record.WhatId != null) {
            types.add(record.WhatId.getSObjectType());
        }
    }
    if(types.contains(HOA__c.sObjectType)) {
        whatIds.putAll([SELECT Name FROM HOA__c WHERE Id = :whatIds.keySet()]);
    }
    if(types.contains(Patient_Event__c.sObjectType)) {
        whatIds.putAll([SELECT Name FROM Patient_Event__c WHERE Id = :whatIds.keySet()]);
    }
    if(types.contains(Review__c.sObjectType)) {
        whatIds.putAll([SELECT Name FROM Review__c WHERE Id = :whatIds.keySet()]);
    }
    if(types.contains(Patient_Satisfaction__c.sObjectType)) {
        whatIds.putAll([SELECT Name FROM Patient_Satisfaction__c WHERE Id = :whatIds.keySet()]);
    }
    if(types.contains(Root_Cause_Analysis__c.sObjectType)) {
        whatIds.putAll([SELECT Name FROM Root_Cause_Analysis__c WHERE Id = :whatIds.keySet()]);
    }
    for(Task record: Trigger.new) {
        sObject whatRecord = whatIds.get(record.WhatId);
        switch on whatRecord {
            when HOA__c hoaRecord {
                record.Object_Label__c= 'HOA' + '('+hoaRecord.Name+ ')';
            }
            when Patient_Event__c patientEventRecord {
                record.Object_Label__c= 'Safety Event' + '('+patientEventRecord.Name+ ')';
            }
            when Review__c reviewRecord {
                record.Object_Label__c= 'Safety Event Review' + '('+reviewRecord.Name+ ')';
            }
            when Patient_Satisfaction__c patientSatisfactionRecord {
                record.Object_Label__c= 'Patient Relations' + '('+patientSatisfactionRecord.Name+ ')';
            }
            when Root_Cause_Analysis__c rootCauseAnalysisRecord {
                record.Object_Label__c= 'RCA' + '('+rootCauseAnalysisRecord.Name+ ')';
            }
            when else {
                record.Object_Label__c = null;
            }
        }
    }
}
2

This Canonical QA nicely covers most questions related to bulkifying queries.

There's another wrinkle here, though, because you are working with Task, which has a polymorphic What relationship. You do not need to write multiple queries for different objects, because you can query for Name through a polymorphic relationship.

SELECT What.Name, What.Type FROM Task WHERE Id IN :Trigger.new

would work in an after insert context (not here, in before insert) and would allow you to query the newly-inserted Tasks with the two data points you need (the Related To name and the type of the Related To object) in a single SOQL query.

You can then iterate over this query and build up a List<Task> of records to update based on the relationship data queried, using t.What.Type to decide what object name to reference and including t.What.Name in the resulting string.

Yes, it will incur one round of trigger recursion because you'll have to update in after context, but it may make up for that with the simplicity of the query and savings on SOQL limits - and you'll have to write a lot less conditional logic.

If more data is required from the Related To object, you can now use polymorphic SOQL to access that via a single query rooted at Task.

  • Thanks David. This is helpful – salmaan shaik Nov 6 '20 at 6:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.