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

1 Answer 1

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();
    }
}
3
  • 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); Mar 3, 2020 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. Mar 3, 2020 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. Mar 3, 2020 at 18:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .