1

I'm trying to get a count of how many placements are on an account. If the record type is subcontract, I want the query to look at the subcontract field on the placement and if the account record type is anything else, I would like it to look at the client field on the placement. I think I have a good solution but it returns with

Batch - too many SOQL Queries 201.

I know I need to remove the SOQL queries from the for loop, but I'm unsure how to get the same result otherwise.

public class plcCounterOnAcct implements Database.Batchable<sObject>, Database.Stateful {

public Database.QueryLocator start(Database.BatchableContext bc) {
    String query = 'SELECT Id, Name FROM Account';
return Database.getQueryLocator(query);
}
public void execute(Database.BatchableContext BC, List<Account> acctList){
    for(Account acc : acctList){
        List<ts2__Placement__c> plcOnSubConAcct = [SELECT Id FROM ts2__Placement__c WHERE Subcontract_Company__c = :acc.Id];
        List<ts2__Placement__c> plcOnAcct = [SELECT Id FROM ts2__Placement__c WHERE ts2__Client__c = :acc.Id];
        if(plcOnSubConAcct.size() > 0){
            acc.Total_Placement_Count__c = plcOnSubConAcct.size();
        }else{acc.Total_Placement_Count__c = plcOnAcct.size();
             }
        }
    try {
        // Update the Account Record
        update acctList;

    } catch(Exception e) {
        System.debug(e);
    }
  }
public void finish(Database.BatchableContext BC){
  }
}
2

Luke, you're committing one of the cardinal sins of Apex development. You wrote a SOQL statement inside of a loop and are hitting governor limits (see here).

You should rewrite your code so that the queries are outside the loop (perhaps into a map?).

This is one possible way:

Set<Id> acctIDsToProcess = new Set<Id>();
for(Account acc : acctList){
   acctIDsToProcess.add(acc.Id);
}
Map<Id, List<ts2__Placement__c>> plcOnSubConAcctMap = new Map<Id, ts2__Placement__c>();

for (ts2__Placement__c plc : [SELECT Subcontract_Company__c, Id FROM ts2__Placement__c WHERE Subcontract_Company__c in  :acctIDsToProcess]) {

     if (! plcOnSubConAcctMap.containsKey(plc.Subcontract_Company__c)) {
       plcOnSubConAcctMap.put(plc.Subcontract_Company__c, new List<ts2__Placement__c>);
     }

     List<ts2__Placement__c> placementPerAccount = plcOnSubConAcctMap.get(plc.Subcontract_Company__c);

     placementPerAccount.add(plc);

     plcOnSubConAcctMap.put(plc.Subcontract_Company__c, placementPerAccount);
}

Map<Id, ts2__Placement__c> plcOnAcctMap = new Map<Id, ts2__Placement__c>();

for (ts2__Placement__c plc : [SELECT ts2__Client__c, Id FROM ts2__Placement__c WHERE ts2__Client__c in  :acctIDsToProcess]) {

    if (! plcOnSubConAcctMap.containsKey(plc.Subcontract_Company__c)) {
       plcOnSubConAcctMap.put(plc.Subcontract_Company__c, new List<ts2__Placement__c>);
     }

     List<ts2__Placement__c> placementPerAccount = plcOnSubConAcctMap.get(plc.Subcontract_Company__c);

     placementPerAccount.add(plc);

     plcOnAcctMap.put(plc.ts2__Client__c, placementPerAccount);
}

for(Account acc : acctList) {
    List<ts2__Placement__c> plcOnSubConAcct = plcOnSubConAcctMap.get(acc.Id);
    List<ts2__Placement__c> plcOnAcctMap = plcOnSubConAcctMap.get(acc.Id);

    if(plcOnSubConAcct != null) {
        acc.Total_Placement_Count__c = plcOnSubConAcct.size();
    } else if(plcOnAcct != null) {
        acc.Total_Placement_Count__c = plcOnAcct.size();
    }
}
| improve this answer | |
  • it looks like you have an illegal assignment from a placement to a list. When trying to account for that, the Total_Placement_Count__c wouldn't go higher than 200 (I'm assuming because of the the batch limit): for(Account acc : acctList) { List<ts2__Placement__c> plcOnSubConAcct = new List<ts2__Placement__c>(); List<ts2__Placement__c> plcOnAcct = new List<ts2__Placement__c>(); ts2__Placement__c plcSub = plcOnSubConAcctMap.get(acc.Id); ts2__Placement__c plcCli = plcOnAcctMap.get(acc.Id); plcOnSubConAcct.add(plcSub); plcOnAcct.add(plcCli); – Luke Higgins Mar 3 at 18:08
  • you were correct on the error but not on the cause. I corrected my code. Keep in mind that you may want to consolidate both queries/loops into one with an OR statement, but I am not sure if that fits your use case. – Sebastian Kessel Mar 3 at 18:12
  • Also, don't add code in comments... it's nearly impossible to read. If you have no choice, surround it by ticks (`) so it reads better. – Sebastian Kessel Mar 3 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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