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
Sangeeta10Sangeeta10 

Need help getting Soql outside for loop

I have a custom object Referral__c which has a lookup to Account and Oppty. Usecase is whenever a new oppty is created and Account on that oppty has a Referral, attach oppty to the Referral as well. My below code works perfectly fine, but would like to know how can I get Soql outside for loop.

 public void AddOpptytoReferral(List<Opportunity> newOpps)
      {
        List<Referral__c> RPtoupdate=new list<Referral__c>();  
        for(Opportunity Opp: newOpps)
       {  
        if(Opp.RecordTypeid=='012j0000000z1iCAAQ' || Opp.RecordTypeid=='012j0000000z1lBAAQ')
        { 
          List<Referral__c> RP=new list<Referral__c>([select id,Opportunity__c from Referral__c where Referral_Account__c= :Opp.AccountId and Expiration_date__c>= today and Opportunity__c=null and Referral_Type__c='Customer Referral' order by createdDate asc limit 1]);
            
            if(RP.size() > 0)
            {
             RP[0].Opportunity__c=Opp.Id;  
             RPtoupdate.add(RP[0]);
            }
         }
       }
          if(RPtoupdate.size()>0){
              update RPtoupdate;}
       }
Amit Chaudhary 8Amit Chaudhary 8
Please try to update your code like below 
public void AddOpptytoReferral(List<Opportunity> newOpps)
{
	List<Referral__c> RPtoupdate=new list<Referral__c>();  
	
	Set<Id> setAccId = new Set<Id>();
	
	for(Opportunity Opp: newOpps)
	{  
		if(Opp.RecordTypeid=='012j0000000z1iCAAQ' || Opp.RecordTypeid=='012j0000000z1lBAAQ')
		{
			setAccId.add(Opp.AccountId);
		}
	}
	
	if(setAccId.size() > 0 )
	{
		List<Referral__c> listRef = [select id,Opportunity__c,Referral_Account__c from Referral__c where Referral_Account__c= :setAccId and Expiration_date__c>= today and Opportunity__c=null and Referral_Type__c='Customer Referral' order by createdDate asc ] ;
		
		Map<String,Referral__c> mapAccWiseReferral = new Map<String,Referral__c>();
		For(Referral__c rf : listRef)
		{
			if(mapAccWiseReferral.containsKey(rf.Referral_Account__c))
			{
				mapAccWiseReferral.put(rf.Referral_Account__c,rf);
			}
		}
		
		
		for(Opportunity Opp: newOpps)
		{  
			if(Opp.RecordTypeid=='012j0000000z1iCAAQ' || Opp.RecordTypeid=='012j0000000z1lBAAQ')
			{
		

				if(mapAccWiseReferral.containsKey(Opp.AccountId) )
				{
					Referral__c RP = mapAccWiseReferral.get(Opp.AccountId);
					RP.Opportunity__c=Opp.Id;  
					RPtoupdate.add(RP);
				}
			}
		}
		
		if(RPtoupdate.size()>0)
		{
			update RPtoupdate;
		}
	}	
}

Let us know if this will help you
 
Akshay_DhimanAkshay_Dhiman
Hi Sangeeta
Try the code below :- 
public void AddOpptytoReferral(List<Opportunity> newOpps){
List<Referral__c> RPtoupdate=new list<Referral__c>();
List<Referral__c> RPrecordList=new list<Referral__c>();
set<Id> AccountIdOnOppoSet = new set<Id>();
map<Id , list<Referral__c>> ReferralIdRecordmap =  new map<Id , list<Referral__c>>();
for(Opportunity Oppo : newOpps){
 if(Oppo.AccountId != Null){
AccountIdOnOppoSet.add(Oppo.AccountId);
  }
}
if(AccountIdOnOppoSet.size() > 0){
RPrecordList =[ select Id ,Opportunity__c ,Referral_Account__c , Expiration_date__c ,Referral_Type__c from Referral__c where Referral_Account__c IN: AccountIdOnOppoSet and Expiration_date__c>= today and Opportunity__c=null and Referral_Type__c='Customer Referral' order by createdDate asc ];
for(Referral__c Refer : RPrecordList){
 if(ReferralIdRecordmap.get(Refer.Referral_Account__c) == Null){
ReferralIdRecordmap.put(Refer.Referral_Account__c , new list<Referral__c>());
}    
ReferralIdRecordmap.get(Refer.Referral_Account__c).add(Refer);
  }
}
System.debug('Check the Values in the Map :- ' + ReferralIdRecordmap); //Here you can check the Value inside the Map.
for(Opportunity Opp: newOpps){  
  if(Opp.RecordTypeid=='012j0000000z1iCAAQ' || Opp.RecordTypeid=='012j0000000z1lBAAQ'){
  if(ReferralIdRecordmap.containsKey(Opp.AccountId){
list<Referral__c> RP = ReferralIdRecordmap.get(Opp.AccountId);
if(RP.size() > 0){
RP[0].Opportunity__c=Opp.Id;  
RPtoupdate.add(RP[0]);
         }
     }
   }
}
 if(RPtoupdate.size()>0){
    update RPtoupdate;
 }

In the above code what am trying to do is am storing the AccountId of Referral__c as keyset with the list of Referral__c as the value. And I can fetch the records of Referral__c inside the for each loop without Soql Query.
Regards
Akshay
Please mark my answer as a solution if it was helpful