You need to sign in to do that
Don't have an account?

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'); ?
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
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
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?
Yes ofcourse. You can use it.
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());
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());
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());
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
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);