Batch - too many SOQL Queries 201

Question

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){
  }
}

Answer 1

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();
    }
}