function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
LaaralLaaral 

Get soql outside for loop.

I'm new in writing apex classes + triggers so I have a problem now with a batch class which has a for loop so DML statement limit goes over, I was reading about bulkifying code, but it seems it's only for triggers not classes(please correct me if I'm wrong)? And how should I change this part of the code if I need to make it work :

        for (Case c : suppportCases) {
            System.Debug('SCHEDULED: CASE ' + c.CaseNumber + ' HANDLING STARTED');
                    
            List<Service_Availability__c> oldSAsPerCase =
                                            [select Name, Status__c, Duration__c, Start_DateTime__c from Service_Availability__c                                                     
                                            where Case__c =: c.Id
                                            order by Name desc];
            
            System.Debug('SCHEDULED: OLD SA COUNT: ' + oldSAsPerCase.Size());
Should I make it this way :

 

List<Service_Availability__c> oldSAsPerCase : [SELECT Name, Status__c, Duration__c, Start_DateTime__c from Service_Availability__c                                                     
                                            where Case__c =: c.Id
                                            order by Name desc];

 

System.Debug('SCHEDULED: OLD SA COUNT: ' + oldSAsPerCase.Size());

 

for (Case c : suppportCases) {
            System.Debug('SCHEDULED: CASE ' + c.CaseNumber + ' HANDLING STARTED'); ?

Best Answer chosen by Admin (Salesforce Developers) 
souvik9086souvik9086

My bad.. Use this. I forgot to append the fields of your object there. 

 

Set<Setup__c> alreadyHandled = new Set<Setup__c>();         

          List<Service_Availability__c> sList = new List<Service_Availability__c>();
            List<Id> idList = new List<Id>();
              for (Case c : suppportCases) {
               idList.add(c.Id);
               }
                 sList = [SELECT ID, Name, Status__c, Duration__c, Start_DateTime__c FROM Service_Availability__c WHERE Case__c IN : idList];
              
            System.Debug('SCHEDULED: CASE ' + c.CaseNumber + ' HANDLING STARTED');
            System.Debug('SCHEDULED: OLD SA COUNT: ' + oldSAsPerCase.Size());

All Answers

souvik9086souvik9086

First of all you told that it is DML limit which goes over. DML limit is not for executing SOQL query within for loop. It is for DML statements like INSERT/UPDATE/DELETE within for loop. You can avoid that by adding them in a list and upserting it outside.

 

In a batch class, by default max 200 records will come at once. So SOQL query will not be an issue within for loop.

For your case it may be something like this

 

List<Service_Availability__c> sList = new List<Service_Availability__c>();

List<Id> idList = new List<Id>();
for (Case c : suppportCases) {
idList.add(c.Id);
}

 

sList = [SELECT ID, Name OwnerId FROM Service_Availability__c WHERE Case__c IN : idList];

//Then work with sList

LaaralLaaral

Sorry I meant too many SOQL query is the status debug log gives.  So I need to take it out of the for loop, but I don't know how should I do it. Can I use the example you gave for this kind problem?

 

 

 

souvik9086souvik9086

Yes ofcourse. You can use it.

LaaralLaaral

But when I put it this way I get a compile error : Compile Error: only aggregate expressions use field aliasing at line 50 column 24

 

  
        // Setups that are already handled. We need to process each Setup only once.
        Set<Setup__c> alreadyHandled = new Set<Setup__c>();         

          List<Service_Availability__c> sList = new List<Service_Availability__c>();
            List<Id> idList = new List<Id>();
              for (Case c : suppportCases) {
               idList.add(c.Id);
               }
                 sList = [SELECT ID, Name OwnerId FROM Service_Availability__c WHERE Case__c IN : idList];
              
            System.Debug('SCHEDULED: CASE ' + c.CaseNumber + ' HANDLING STARTED');
            System.Debug('SCHEDULED: OLD SA COUNT: ' + oldSAsPerCase.Size());
                                                       

souvik9086souvik9086

My bad.. Use this. I forgot to append the fields of your object there. 

 

Set<Setup__c> alreadyHandled = new Set<Setup__c>();         

          List<Service_Availability__c> sList = new List<Service_Availability__c>();
            List<Id> idList = new List<Id>();
              for (Case c : suppportCases) {
               idList.add(c.Id);
               }
                 sList = [SELECT ID, Name, Status__c, Duration__c, Start_DateTime__c FROM Service_Availability__c WHERE Case__c IN : idList];
              
            System.Debug('SCHEDULED: CASE ' + c.CaseNumber + ' HANDLING STARTED');
            System.Debug('SCHEDULED: OLD SA COUNT: ' + oldSAsPerCase.Size());

This was selected as the best answer
LaaralLaaral

Sorry to bother you again, but now I have a problem with thisCompile Error: Variable does not exist: c.Related_Setup__c at line 56 column 138 and it's because it's not in the for loop like it was before. How could I change the code so the highlighted are is also processed. Should I try and fecth it from created  sList?  

public void HandleServiceAvailabilityChange(Date currentDate) {
        
        /*
        1) Fetch not closed Support Cases
        2) Fetch valid Service Availability objects per Case
        3) Sort SAs by a) Case b) Name
        4) Update last SA with duration until end of the month
        5) Create new SA from the beginning of the new month (with same Fault Classification)       
        */
        RecordType recordType = [select Id, Name from RecordType where SObjectType = 'Case' and Name = 'Support' LIMIT 1];
        
        System.Debug('SCHEDULED: SUPPORT CASE REC TYPE ID: ' + recordType.Id);              
                               
        List<Case> suppportCases = [select Id, Fault_Classification__c, Setup_Name__c, CaseNumber, Related_Setup__c,Status_Explanation__c
                            from Case                                                       
                            where RecordTypeId =: recordType.Id
                            and IsClosed =: false                           
                            order by CreatedDate desc];

        System.Debug('SCHEDULED: SUPPORT CASE COUNT: ' + suppportCases.Size());     
        ServiceAvailability saHelper = new ServiceAvailability();
       
        // Setups that are already handled. We need to process each Setup only once.
        Set<Setup__c> alreadyHandled = new Set<Setup__c>();         

          List<Service_Availability__c> sList = new List<Service_Availability__c>();
            List<Id> idList = new List<Id>();
            for (Case c : suppportCases) {
               idList.add(c.Id);
             System.Debug('SCHEDULED: CASE ' + c.CaseNumber + ' HANDLING STARTED');
               }
                 sList = [SELECT ID, Name, Status__c, Duration__c, Start_DateTime__c FROM Service_Availability__c WHERE Case__c IN : idList];
              
            System.Debug('SCHEDULED: OLD SA COUNT: ' + oldSAsPerCase.Size());
                                                       
            if (oldSAsPerCase.Size() > 0) {             
                Setup__c relatedSetup = [select Id, Name, Service_Availability__c, Contract__c, Cost_Center__c from Setup__c where Id =: c.Related_Setup__c];                
                
                Id hoursToUse = saHelper.GetHoursToUse(c, relatedSetup);
                if(hoursToUse != null && relatedSetup != null) {                                            
                RecordType recordTypeIncident = [select Id, Name from RecordType where SObjectType = 'Service_Availability__c' and Name = 'Incident' LIMIT 1];
                
                System.debug('SCHEDULED: DATE IN LAST SA: ' + oldSAsPerCase[0].Start_DateTime__c);
                System.debug('SCHEDULED: CURRENT DATE: ' + DateTime.Now());

souvik9086souvik9086

this is another query. in the above posts you showed one. Let me think for this. Because you need this here as well

 

Id hoursToUse = saHelper.GetHoursToUse(c, relatedSetup);

 

Here c is also not there

LaaralLaaral

Hi, I started making this kind of change in my code

global class ServiceAvailabililtyUpdaterForCase implements Schedulable,
                                                           Database.Stateful/*,
                                                           Database.Batchable<sObject>*/ {

    // Implement Schedulable interface function for entry function to schedule this batch
    global void execute(SchedulableContext sc){
        
        ServiceAvailabililtyUpdaterForCase updater = new ServiceAvailabililtyUpdaterForCase();
        Date currentDate = Date.newinstance(DateTime.Now().Year(), DateTime.Now().Month(), DateTime.Now().Day());          
        updater.HandleServiceAvailabilityChange(currentDate);     
    }    
    
    /*
    * Execute method of Batchable interface
    */
    global void execute( Database.BatchableContext BC, List<sObject> records ) {
        System.debug('ServiceAvailabililtyUpdaterForCase.execute batch');               
    }       
            
    public void HandleServiceAvailabilityChange(Date currentDate) {
        
        /*
        1) Fetch not closed Support Cases
        2) Fetch valid Service Availability objects per Case
        3) Sort SAs by a) Case b) Name
        4) Update last SA with duration until end of the month
        5) Create new SA from the beginning of the new month (with same Fault Classification)       
        */
        RecordType recordType = [select Id, Name from RecordType where SObjectType = 'Case' and Name = 'Support' LIMIT 1];
        
        System.Debug('SCHEDULED: SUPPORT CASE REC TYPE ID: ' + recordType.Id);              
                               
        List<Case> suppportCases = [select Id, Fault_Classification__c, Setup_Name__c, CaseNumber, Related_Setup__c,Status_Explanation__c
                            from Case                                                       
                            where RecordTypeId =: recordType.Id
                            and IsClosed =: false                           
                            order by CreatedDate desc];

        System.Debug('SCHEDULED: SUPPORT CASE COUNT: ' + suppportCases.Size());     
        ServiceAvailability saHelper = new ServiceAvailability();
        
        // Setups that are already handled. We need to process each Setup only once.
       Set<Setup__c> alreadyHandled = new Set<Setup__c>();         

          List<Service_Availability__c> sList = new List<Service_Availability__c>();
            List<Id> idList = new List<Id>();
            for (Case c : suppportCases) {
               idList.add(c.Id);
             System.Debug('SCHEDULED: CASE ' + c.CaseNumber + ' HANDLING STARTED');
               }
                 sList = [SELECT ID, Name, Status__c, Duration__c, Start_DateTime__c, case__r.Id FROM service_availability__c WHERE case__r.Id IN : idList order by Name desc];
                 ID hoursToUse = [select related_setup__r.contract__r.contract_agreement__r.business_hours__c from case where Id IN : idList];
                                                        
            if (sList.Size() > 0) {             
              //  Setup__c relatedSetup = [select Id, Name, Service_Availability__c, Contract__c, Cost_Center__c from Setup__c where Id =: c.Related_Setup__c];      
              RecordType recordTypeIncident = [select Id, Name from RecordType where SObjectType = 'Service_Availability__c' and Name = 'Incident' LIMIT 1];
                        
              for (Service_Availability__c c : sList) {  
               // Id hoursToUse = saHelper.GetHoursToUse(c, relatedSetup);
                if(hoursToUse != null && relatedSetup != null) {                                            
                
                System.debug('SCHEDULED: DATE IN LAST SA: ' + sList[0].Start_DateTime__c);
                System.debug('SCHEDULED: CURRENT DATE: ' + DateTime.Now());

 

Does this look right way to create map ? I'm not sure is those Where Id sentences right because only lookup connecting Service_availability + Case is case__ c lookup field? 

 

Service_Availability__c c = [SELECT Id, name, recordtype, case__r.Id, duration__c, fault_classification__c, object_added_automatically__c, setup__c, start_datetime__c,status__c from service_availability__c  where id = case.case__c ];

 

Case Id = [Select Id, Name, RecordType, service_availability__c from case where Id = c.case__c];

Map<Id, List> service_availability = new Map<Id, List>
Map<cId, Set<List>> M = new Map <Id, Set<List>>();
List value = m.get(Id);