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
Ryan Mason 5Ryan Mason 5 

Take SOQL out of For loop

 
I need to take this SOQL query out of my for loop. I've been researching using the Map method but honestly stuck when it comes to applying the logic to what I have:

for (Lead newLead : newLeadList)
        {
            system.debug('here ryan');
            Id referringAccount = newLead.Referring_Account__c;
            Account accountPulls = [SELECT Account_Pulls_Medication_History__c FROM Account WHERE Id =: referringAccount];
                
            system.debug('here ryan'+accountPulls);
            if (accountPulls.Account_Pulls_Medication_History__c)
            {
                system.debug('here ryan2');
                if(newLead.Medication_History_Pull__c == null)
                {
                    newLead.Medication_History_Pull__c = 'Pending';
                    system.debug('here ryan3');
                }
            }
        }


Any help would be much appreciated
 
 
Best Answer chosen by Ryan Mason 5
Andrew GAndrew G
it would be a psuedo code of 
get Account Ids
do Select statement for accounts
build a map of the accounts
Loop the list 
check the map 
update lead as required.

so: 
List<Id> accIds = new List<Id>();
for (Lead newLead : newLeadList)
{
    accIds.add(newLead.Referring_Account__c);
}
//in this case, the SELECT statement pulls directly into the map as the Keyset is the Id of the Account
//if the Key was to be another field, then you would need an additional loop to build that map.
Map<Id,Account> accountMap =  [SELECT Id, Account_Pulls_Medication_History__c FROM Account WHERE Id IN :accIds];
                
System.debug('here ryan'+ accountMap );

for(Lead newLead : newLeadList) 
{
    if(accountMap.containsKey(newLead.Referring_Account__c))
    {
        if (accountMap.get(newLead.Referring_Account__c).Account_Pulls_Medication_History__c) 
            {
                system.debug('here ryan2');
                if(newLead.Medication_History_Pull__c == null)
                {
                    newLead.Medication_History_Pull__c = 'Pending';
                    system.debug('here ryan3');
                }
            }
        }
    }
}

The above code is supplied uncompiled, but should be close to what you need 

regards
Andrew
 

All Answers

Andrew GAndrew G
it would be a psuedo code of 
get Account Ids
do Select statement for accounts
build a map of the accounts
Loop the list 
check the map 
update lead as required.

so: 
List<Id> accIds = new List<Id>();
for (Lead newLead : newLeadList)
{
    accIds.add(newLead.Referring_Account__c);
}
//in this case, the SELECT statement pulls directly into the map as the Keyset is the Id of the Account
//if the Key was to be another field, then you would need an additional loop to build that map.
Map<Id,Account> accountMap =  [SELECT Id, Account_Pulls_Medication_History__c FROM Account WHERE Id IN :accIds];
                
System.debug('here ryan'+ accountMap );

for(Lead newLead : newLeadList) 
{
    if(accountMap.containsKey(newLead.Referring_Account__c))
    {
        if (accountMap.get(newLead.Referring_Account__c).Account_Pulls_Medication_History__c) 
            {
                system.debug('here ryan2');
                if(newLead.Medication_History_Pull__c == null)
                {
                    newLead.Medication_History_Pull__c = 'Pending';
                    system.debug('here ryan3');
                }
            }
        }
    }
}

The above code is supplied uncompiled, but should be close to what you need 

regards
Andrew
 
This was selected as the best answer
Ryan Mason 5Ryan Mason 5
Thank you!! It works perfectly and best of all I actually understand it now.