+ Start a Discussion
Joe CoryaJoe Corya 

Moving soql queries out of for loop

My apex skill level is not quite there yet and we would like to have this trigger up to best practice standards as we hit the governor limit randomly. How would you simplify this code to reduce soql queries?
 
trigger WorkAssign on VanaHCM__Worker_Assignment__c (after insert, after update) 
{
    if(Trigger.isInsert)
    {
        VanaHCM__Worker_Assignment__c gplObject = new VanaHCM__Worker_Assignment__c(); // This takes all available fields from the required object. 
        Map<String, Schema.SObjectField> mapFields = Schema.SObjectType.VanaHCM__Worker_Assignment__c.fields.getMap(); 
     
   
                string objString =  string.valueOf(gplObject);
                
                for(VanaHCM__Worker_Assignment__c gpl : trigger.new)
                {
                Id hcmID = (Id) gpl.get('VanaHCM__Worker_Assignment__c');
                sObject idObject = [Select VanaHCM__Employee_ID__c From VanaHCM__Worker__c where Id = :hcmID];  
                string vpID = (string) idObject.get('VanaHCM__Employee_ID__c');     
                          
                    for (String str : mapFields.keyset()) 
                    { 
                       
                        if (str == 'VanaHCM__Position__c')
                        {  
                            if (gpl.get('VanaHCM__Position__c') != null)
                            {
                                String val = (string) gpl.get(str);
                                sObject find = [Select Name from VanaHCM__Position__c where Id = :val];
                                string PC = (string) find.get('Name'); 
                                WebServiceCallout.sendNotification(vpID, str, PC, objString);
                        
                                sObject find1 = [Select Insurance_Code__c from VanaHCM__Position__c where Id = :val];
                                string co = (string) find1.get('Insurance_Code__c');
                                    if (co != '')
                                    {
                                        sObject code = [Select Name from Insurance_Code__c where Id = :co];
                                        string name = (string) code.get('Name');
                                        WebServiceCallout.sendNotification(vpID, 'inscode' , name, objString);
                                    }
                            }
                        }   
                        
                        else if (str == 'VanaHCM__Pay_Group_Worker_Assign__c')
                        {
                        
                                String val = (string) gpl.get(str);
                                if (val != null)
                                {
                                    sObject find = [Select Name from VanaHCM__Pay_Group__c where Id = :val];
                                    string PG = (string) find.get('Name');
                                    WebServiceCallout.sendNotification(vpID, str, PG, objString);
                                }
                       
                        }
                        else if (str == 'Craft__c' && gpl.get('VanaHCM__Worker_Event__c') != 'a45500000006KVHAA2')
                        {
                                String val = (string) gpl.get(str);
                                if (val != null)
                                {
                                    sObject find = [Select Name from Craft__c where Id = :val];
                                    String val1 = (string) find.get('Name');
                                    WebServiceCallout.sendNotification(vpID, str, val1, objString);
                                }
                                else
                                {
                                    WebServiceCallout.sendNotification(vpID, str, '', objString);   
                                }
                        
                        }
                        else if (str == 'Class__c' && gpl.get('VanaHCM__Worker_Event__c') != 'a45500000006KVHAA2')
                        {
                                String val = (string) gpl.get(str);
                                if (val != null)
                                {
                                    sObject find = [Select Name from Class__c where Id = :val];
                                    String val1 = (string) find.get('Name');
                                    WebServiceCallout.sendNotification(vpID, str, val1, objString);
                                }
                                else
                                {
                                    WebServiceCallout.sendNotification(vpID, str, '', objString);   
                                }
                        }
                        else if (str == 'VanaHCM__Department__c')
                        {
                                String val = (string) gpl.get(str);
                                if (val != null)
                                {
                                    sObject find = [Select Name from VanaHCM__Department__c where Id = :val];
                                    String val1 = (string) find.get('Name');
                                    WebServiceCallout.sendNotification(vpID, str, val1, objString);
                                }
                        
                        }
                        else if(gpl.get(str) == 'a45500000006KVHAA2')
                        {
                                datetime val = (datetime) gpl.get('VanaHCM__Termination_Date__c');
                                WebServiceCallout.sendNotification3(vpID, 'terminate', val, objString);
                        }
                        else if (gpl.get(str) == 'a45500000006KVJAA2')
                        {
                                datetime val = (datetime) gpl.get('VanaHCM__Effective_Date_Work_Assign__c');
                                WebServiceCallout.sendNotification3(vpID, 'rehire', val, objString);
                        }
                          else if (gpl.get('VanaHCM__Not_Eligible_for_Rehire__c') == 1)
                        {
                               
                           WebServiceCallout.sendNotification(vpID, 'NOrehire', 'Y', objString);
                        }
                       
                       }
                    
                
            }
        
        }
    else if(Trigger.isUpdate)
    {
        VanaHCM__Worker_Assignment__c gplObject = new VanaHCM__Worker_Assignment__c(); // This takes all available fields from the required object. 
        Map<String, Schema.SObjectField> mapFields = Schema.SObjectType.VanaHCM__Worker_Assignment__c.fields.getMap(); 
      
        for(VanaHCM__Worker_Assignment__c gpl : trigger.new)
        {
.....

 
Amol ChAmol Ch
Hi Joe,

As a best practice, Avoid SOQL query inside for loop. Write all the soql query outside for loop. your problem will solve.

Please refer below article for more help.

https://trailhead.salesforce.com/en/modules/apex_triggers/units/apex_triggers_bulk
https://developer.salesforce.com/page/Best_Practice:_Avoid_SOQL_Queries_Inside_FOR_Loops
https://developer.salesforce.com/page/Apex_Code_Best_Practices