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
Tony DeMarcoTony DeMarco 

Multiple queries for Apex batch job -- too many queries error

So the use case is as follows:  On the Account page there is the territories field that for a given account will show whatever territories are assigned to that particular Account.  So, as an example, Acme Corp. may show a territories field of National, Northeast, and NY on the page.  However, in reporting, SFDC returns for the Account by territories report individual line items for Acme Corporation for National, Northeast and NY respectively.  The consumer requirement here is to report on territories for Accounts in the same manner they are displayed on the Account page: all concatenated within the same field.

So given there is no way to do this via formula, I investigated doing via Apex.  It turned out that 4 objects would be needed to produce the territory relationship for Accounts: Account, AccountShare, Group, & Territory.  However, the kicker is that basically 3 separate queries are needed to accoumplish this.  The parent-child relationships are not accessble in SOQL, and of course you can't do joins between objects like you can in T-SQL unless there is that parent-child relationship.

So I attempted a batch job to somehow put this together.  However, my approach is wrong in that I am running into query limits with my compiled code, and I am not sure how to structure this to get around the problem.  Below is my compliled code that errors out.  Guidance would be appreciated.  Thanks in advance, TDM
 
global class batchTerritoryCustomUpdate implements Database.Batchable<sObject>

{
    //The purpose of this batchClass is to return concatenated Territory Names to the Account object custom field Account_Territory__c for reporting purpose specific to user requests
    
    global Database.QueryLocator start(Database.BatchableContext BC)
    
    {  
        String query = 'Select Id From Account';
        return Database.getQueryLocator(query);      // returns AccountId and UserOrGroupId for queries below.  

    }
    global void execute(Database.BatchableContext BC, List<Account> scope)
    {
        Integer i = 0;
                
        for(Account updateAccount : scope)
        {    
            String acctID = updateAccount.Id;
            String relId;
            String groupId;
            String matchinAcctId;
            List <AccountShare> aShare = new List <AccountShare>();
            aShare = [Select AccountId, UserOrGroupId from AccountShare Where RowCause = 'Territory' and AccountId =: acctID];
            Do{
            for(AccountShare ash : aShare)  // setup accounts for update
            { 
                groupId = ash.UserOrGroupId;
                List <Group> pickGroup = new List<Group>();
                pickGroup = [Select RelatedId from Group where Type ='Territory' and Id =: groupId];
                
                For(Group selectGroup : pickGroup)  //loop through Group data
                {
                    
                    relId = selectGroup.RelatedId;
                    String terrName = '|';
                    List <Territory> terra = new List <Territory>();
                    terra = [Select Name from Territory Where Id =: relId];
                    for(Territory terraNova : terra)  //loop through Territory data
                    {
                        List <Account> matchAccount = new List <Account>();
                        matchAccount = [Select Id from Account where Id =: acctId];
                        For(Account finalAcct : matchAccount)
                        { 
                                terrName = terrName + terraNova.Name + '|'; 
                                matchinAcctId = finalAcct.Id;
                                finalAcct.Id = acctId;
                               
                            updateAccount.Account_Territory__c = terrName;  //write the concatenated territories to the custom Account field
                        
                        }
                     }
                 }
                
                
                }
            } while(matchinAcctId==acctId);
           update scope;
        }    
    }    
       
    global void finish(Database.BatchableContext BC)
    {
    }
    
    
}

 
Chamil MadusankaChamil Madusanka
In line 24, 30, you have queried inside the for loop. Avoid that.

refer this for more details: https://developer.salesforce.com/page/Best_Practice:_Avoid_SOQL_Queries_Inside_FOR_Loops (https://developer.salesforce.com/page/Best_Practice:_Avoid_SOQL_Queries_Inside_FOR_Loops" target="_blank)

If you get the answer, please mark it as the correct answer. It will be a help to others who are facing the same problem later.
 
Amit Chaudhary 8Amit Chaudhary 8
I Found two issue in your code
1) Query Inside Loop line number 24 and 30
2) DML inside Loop . Line number 58

 
Tony DeMarcoTony DeMarco
So I basically found no way to get this done via batch without having a Select within a For loop.  However, I did work around it by creating a trigger and a helper class.  Afterwards I just did a mass update to cause the trigger to execute and got the end result.  I needed the trigger anyway on a go forward basis to maintain the data.  The code follows:
trigger updateCustomTerritory on Account(after insert, after update) 
{
    
   if(checkRecursive.runOnce())
   {
    for(Account a : Trigger.new)
   {
    	updateAccountTerritory f = new updateAccountTerritory();
       	f.theAccountId(a.Id);
   } 
   }
   
}


--------------------------------helper class----------------------------------------------------------------------

public  class updateAccountTerritory
{
	public void theAccountId(String acctId)
    {
    	List groupId = new List();
        List relatedId = new List();
        String terra = ' | ';
        integer  i = 0;
        
        List aShare = new List();
        aShare = [Select AccountId, UserOrGroupId from AccountShare Where RowCause = 'Territory' and AccountId =: acctID];
        
        if(aShare != null && aShare.size() > 0)
        {
            for(AccountShare ash : aShare)
            {
               
                groupId.add(ash.UserOrGroupId);
                i = i + 1;
               
            }
            
       	}
       
        i = 0;
        List g = new List();
        g = [Select RelatedId from Group where Type ='Territory' and Id in: groupId];
        
        if(g != null && g.size() > 0)
        {
            for (Group grp : g)
            {
               relatedId.add(grp.RelatedId);
               i = i + 1;          
                
            }
        }
    
		List t = new List();
        t = [Select Name from Territory Where Id in:relatedId];
        
        if(t != null && t.size() > 0)
        {
            for(Territory terr : t)
            {
                terra = terra + terr.Name + ' | ';
            }
        }
        
        if (terra !=null)
        {
            List a = new List();
            a = [Select Id, Account_Territory__c from Account where Id =:acctId];
            for(Account upd : a)
            {
                upd.Account_Territory__c = terra;
            }
            update a;            
        }   
        }
    }