function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
JagadeshJagadesh 

Avoid SOQL query from for loop

HI All,

Im facing some issue where i need to avoid SOQL query in for loop.My code is:
for(Case casex:cases)
        {
            
            if(casex.Account == null )
            {
               
               List accounts = [SELECT id from Account c WHERE c.Accounts_Reference_Code__c =:casex.Accounts_Reference_Code__c AND c.Accounts_Ledger__c = :casex.Origin];
               
               if(accounts.size() < 1)
               {
                   casex.Description = 'No Account Found'; 
               } 
               else if(accounts.size() > 1)
               {
                   casex.Description = 'Multiple Accounts Found'; 
               }
               else
               {
                   casex.AccountId = accounts[0].id;
               }
            }

I need to remove SOQL query from the for Loop.Any help is greatly appreciated.
Shrikant BagalShrikant Bagal
Hello Jagadesh,

please try following code:
  
Set setAccountsReferenceCode = new Set();
Set setAccountsLedger = new Set();
Set setCaseId = new Set();
for(Case casex:cases)
        {
		setAccountsReferenceCode.add(casex.Accounts_Reference_Code__c);
		setCaseId.add(casex.Id);			
		setAccountsLedger.add(casex.Origin);
	}

List accounts = [SELECT id from Account c WHERE c.Accounts_Reference_Code__c IN :setAccountsReferenceCode AND c.Accounts_Ledger__c IN :setAccountsLedger];

for(Case casex:cases)
        {
		Integer count = 0;
		Set accId = new Set();
		for(Account acc : accounts)
		{
			if(acc.Accounts_Reference_Code__c =:casex.Accounts_Reference_Code__c AND acc.Accounts_Ledger__c = :casex.Origin){
				count++;
				accId.add(acc.Id);		
			}
		}
		if(count = 0){
			casex.Description = 'No Account Found'; 
		}
		else if(count > 1){
			casex.Description = 'Multiple Accounts Found'; 
		}
		else{
			casex.AccountId = accId[0];		
		}
	}

 
James LoghryJames Loghry
Maps are great for this kind of use case.  However, in your code you have two criteria for fetching the Accounts, so it gets a little tricky.  You can do this a few different ways, but as I show in the code below, I'm using a compound key for the map to move the SOQL outside your for loop.
  
Set ledgers = new Set();
Set refCodes = new Set();
for(Case casex:cases){
    if(casex.Account == null ){
        ledgers.add(casex.Origin);
        refCodes.add(casex.Accounts_Reference_Code__c);
    }
}

Map> accountMap = new Map>();
for(Account a :  [SELECT Accounts_Reference_Code__c,Accounts_Ledger__c from Account WHERE Accounts_Reference_Code__c in :refCodes AND Accounts_Ledger__c in :origins]){  
    String key = a.Accounts_Ledger__c + ':::' + a.Accounts_Reference_Code__c;
    List accounts = accountMap.get(key);
    if(accounts == null){
        accounts = new List();
        accountMap.put(key,accounts);
    }
    accounts.add(a);
}

for(Case casex : cases){
    String key = casex.Origin + ':::' + casex.Accounts_Reference_Code__c;
    List accts = accountMap.get(key);
    if(accts == null || accts.isEmpty()){
        casex.Description = 'No Account Found'; 
    }else if(accounts.size() > 1){
        casex.Description = 'Multiple Accounts Found'; 
    }else{
        casex.AccountId = accounts[0].id;
    }
}

Keep in mind that this code likely has errors in it, but it should get you started down the right path.
James LoghryJames Loghry
That should be a Map of Strings and Accounts by the way.. It keeps trying to escape the markup as HTML.

Also, Shrikants example won't work for cases where there are multiple Reference Codes for the same Ledger, or multiple Ledgers for the same Reference code, as this can introduce false positive matches for your cases.