+ Start a Discussion
yarramyarram 

System.LimitException: Too many SOQL queries: 101

Hi All,

i am getting too many SOQL queries error when my below class executed. i noticed that soql Queries inside the for loop but i don't have the idea HOW can I write the SOQL queries OUTSide the For LOOP as per my requirement. Below is my Util calss for my trigger. Please help me on this 


public class OLIUtil{
     
    public static void createProject(String OppID){
        
    List<Attachment> aList;
        List<Attachment> newAList=new List<Attachment>();
        
        Project__c newProj;
    
    List<Project_LI__c> PrjLIList=new List<Project_LI__c>();
    
    List<OpportunityLineItem> OppProdList=[Select id,TotalPrice,Subtotal,Product2.Name,ProductCode,Product2.Id,Opportunity.Name,
                             from OpportunityLineItem where Opportunity.Id=:newOpp.Id];
        // make sure we don't create duplicates project records
             if ([select count() from Project__c where Project_Name__c = :NewOpp.Id] == 0) 
             {
                 newProj=new Project__c();
                 newProj.Project_Name__c=NewOpp.Id;
                 newProj.Project_Stage__c='In Progress';
                 insert newProj;
                 Project_LI__c newPrjLI;
                     for(OpportunityLineItem oppLI:OppProdList)
                     {
                            newPrjLI=new Project_LI__c();
                            newPrjLI.LI_Name__c=oppLI.Product2.Name;
                            newPrjLI.Project_Name__c=newProj.Id;
                            newPrjLI.LI_Number__c=oppLI.ProductCode;
                            newPrjLI.Quantity__c=oppLI.Quantity;                      
                            
                            PrjLIList.add(newPrjLI);
                     }
                 insert PrjLIList;
        
                 List<Project_LI__c> addedLI=[select id,name,Project_Name__c,LI_Name__c from Project_LI__c where Project_Name__c=:newProj.Id];
                 
         for(Project_LI__c newAddedLI:addedLI){   // In this for Loop I have written 3 SOQL queries. How can I write these 3 SOQL s to Out side?
                    
            Project_LI__c ddd=[Select id,name,LI_Name__c,Project_Name__c from Project_LI__c where id=:newAddedLI.Id];
                    
            Product2 oppProd=[Select id,name from Product2 where name=:ddd.LI_Name__c];
                    
            aList=[Select Id,name,body,ParentId from Attachment where ParentId=:oppProd.Id];
                    
                    if(aList.size()!=0){
                        for(Attachment aa:aList)
                         {
                             Attachment a=new Attachment();
                                 a.name=aa.name;
                                 a.body=aa.body;
                                 a.ParentId=newAddedLI.id;
                                newAList.add(a);
                         }
                    }
                 }
                 insert newAList;
             }
    }
}

Thanks,
Yarram
Best Answer chosen by yarram
Jigar.LakhaniJigar.Lakhani
Sorry, forget about else condition for quanitity. Because of this else condition of quantity you must need to use "Before Insert".
Here latest code for opportuinty line item trigger.

Apex Trigger:
trigger QuantityBreakDownTrigger on OpportunityLineItem (BEFORE INSERT) {
	if (Trigger.IsBefore && Trigger.IsInsert) {
		
		Set<Id> setOpportunityId = new Set<Id>();
		for (OpportunityLineItem objOLI:Trigger.new) {
			if (objOLI.OpportunityId != null) {
				setOpportunityId.Add(objOLI.OpportunityId);
			}
		}
		
		Map<Id,Opportunity> mapIdOpportunity = new Map<Id,Opportunity>([SELECT Id,Name,StageName,Amount,CloseDate,Sample_Receipt__c,Study_Number__c 
																		FROM Opportunity WHERE Id In:setOpportunityId]);
		
		List<OpportunityLineItem> listInsertOptyLineItems = new List<OpportunityLineItem>();
		
		for (OpportunityLineItem objOLI:Trigger.new) {
			
			Opportunity objOpportunity = mapIdOpportunity.Get(objOLI.OpportunityId);
			
			if (mapIdOpportunity != null && mapIdOpportunity.size() > 0 && mapIdOpportunity.Get(objOLI.OpportunityId) != null) {
				if (objOLI.Quantity > 1) {
					for(Integer intI=1; intI <= objOLI.Quantity; i++){
						OpportunityLineItem objNewOLI = new OpportunityLineItem();
						objNewOLI.Quantity = 1;
						objNewOLI.ServiceDate = objOLI.ServiceDate;
						objNewOLI.OpportunityId = objOpportunity.Id;
						objNewOLI.PricebookEntryId = objOLI.PricebookEntryId;
						objNewOLI.UnitPrice = objOLI.UnitPrice;
						objNewOLI.TotalPrice = objOLI.TotalPrice;
						objNewOLI.Description = objOLI.Description;
						objNewOLI.Discount = objOLI.Discount;
						objNewOLI.Study_Number__c = objOpportunity.Study_Number__c;
						objNewOLI.Sample_Receipt__c = objOpportunity.Sample_Receipt__c;
						listInsertOptyLineItems.Add(objNewOLI);
					}
				} else {
					objOLI.Study_Number__c = objOpportunity.Study_Number__c;
					objOLI.Sample_Receipt__c = objOpportunity.Sample_Receipt__c;
				}
			}
		}
		
		if (listInsertOptyLineItems != null && listInsertOptyLineItems.size() > 0) {
			Insert listInsertOptyLineItems;
		}
	}
}

Thanks and Cheers,
Jigar

 

All Answers

Jigar.LakhaniJigar.Lakhani
Hello,

Please try with below code, I have removed SOQL query from loop as well as add alternate for it.
 
public class OLIUtil{
     
    public static void createProject(String OppID){
        
    List<Attachment> aList;
	List<Attachment> newAList=new List<Attachment>();
        
        Project__c newProj;
    
    List<Project_LI__c> PrjLIList=new List<Project_LI__c>();
    
    List<OpportunityLineItem> OppProdList=[Select id,TotalPrice,Subtotal,Product2.Name,ProductCode,Product2.Id,Opportunity.Name,
                             from OpportunityLineItem where Opportunity.Id=:newOpp.Id];
        // make sure we don't create duplicates project records
			if ([select count() from Project__c where Project_Name__c = :NewOpp.Id] == 0) {
				
				newProj=new Project__c();
				newProj.Project_Name__c=NewOpp.Id;
				newProj.Project_Stage__c='In Progress';
				insert newProj;
				Project_LI__c newPrjLI;
				
				for (OpportunityLineItem oppLI:OppProdList) {
					newPrjLI=new Project_LI__c();
					newPrjLI.LI_Name__c=oppLI.Product2.Name;
					newPrjLI.Project_Name__c=newProj.Id;
					newPrjLI.LI_Number__c=oppLI.ProductCode;
					newPrjLI.Quantity__c=oppLI.Quantity;
					PrjLIList.add(newPrjLI);
				}
				insert PrjLIList;

				List<Project_LI__c> addedLI = new List<Project_LI__c>([select id,name,Project_Name__c,LI_Name__c from Project_LI__c where Project_Name__c=:newProj.Id]);
				
				Set<String> setProjectLINames = new Set<String>();
				for (Project_LI__c newAddedLI:addedLI) {
					setProjectLINames.Add(newAddedLI.LI_Name__c);
				}
				
				List<Product2> listProducts = new List<Product2>([SELECT Id,Name FROM Product2 WHERE Name In:setProjectLINames]);
				Map<String, Product2> mapNameProduct = new Map<String, Product2>();
				for (Product2 objProduct:listProducts) {
					if (!mapNameProduct.ContainsKey(objProduct.Name)) {
						mapNameProduct.Put(objProduct.Name, objProduct);
					}
				}
				
				aList = new List<Attachment>([SELECT Id,name,body,ParentId FROM Attachment WHERE ParentId In:listProducts]);
				Map<Id, List<Attachment>> mapProductIdAttachments = new Map<Id, List<Attachment>>();
				for (Attachment objAttachment:aList) {
					if (!mapProductIdAttachments.ContainsKey(objAttachment.ParentId)) {
						List<Attachment> listAttachments = new List<Attachment>();
						listAttachments.Add(objAttachment);
						mapProductIdAttachments.Put(objAttachment.ParentId, listAttachments);
					} else {
						mapProductIdAttachments.Get(objAttachment.ParentId).Add(objAttachment);
					}
				}
				
				for (Project_LI__c newAddedLI:addedLI) {   // In this for Loop I have written 3 SOQL queries. How can I write these 3 SOQL s to Out side
					if (mapNameProduct != null && mapNameProduct.size() > 0 && mapNameProduct.Get(newAddedLI.LI_Name__c) != null) {
						Id productId = mapNameProduct.Get(newAddedLI.LI_Name__c).Id;
						if (mapProductIdAttachments != null && mapProductIdAttachments.size() > 0 && mapProductIdAttachments.Get(productId) != null) {
							List<Attachment> listAttachments = mapProductIdAttachments.Get(productId);
							if (listAttachments != null && listAttachments.szie() > 0) {
								for (Attachment aa:listAttachments) {
									Attachment a=new Attachment();
									a.name=aa.name;
									a.body=aa.body;
									a.ParentId=newAddedLI.id;
									newAList.add(a);
								}
							}
						}
					}
				}
				insert newAList;
            }
    }
}

Let me know if you have any question/issue.

Thanks and Cheers,
Jigar
John PipkinJohn Pipkin
public class OLIUtil{
     
    public static void createProject(String OppID){
        
	    
        List<Attachment> newAList=new List<Attachment>();
	        
        Project__c newProj;
	    
	    List<Project_LI__c> PrjLIList=new List<Project_LI__c>();
	    
	    List<OpportunityLineItem> OppProdList=[Select id,TotalPrice,Subtotal,Product2.Name,ProductCode,Product2.Id,Opportunity.Name,
	                             from OpportunityLineItem where Opportunity.Id=:newOpp.Id];
	        // make sure we don't create duplicates project records
         if ([select count() from Project__c where Project_Name__c = :NewOpp.Id] == 0) 
         {
             newProj=new Project__c();
             newProj.Project_Name__c=NewOpp.Id;
             newProj.Project_Stage__c='In Progress';
             insert newProj;
             Project_LI__c newPrjLI;
                 for(OpportunityLineItem oppLI:OppProdList)
                 {
                        newPrjLI=new Project_LI__c();
                        newPrjLI.LI_Name__c=oppLI.Product2.Name;
                        newPrjLI.Project_Name__c=newProj.Id;
                        newPrjLI.LI_Number__c=oppLI.ProductCode;
                        newPrjLI.Quantity__c=oppLI.Quantity;                      
                        
                        PrjLIList.add(newPrjLI);
                 }
             insert PrjLIList;
    
             List<Project_LI__c> addedLI=[select id,name,Project_Name__c,LI_Name__c from Project_LI__c where Project_Name__c=:newProj.Id];
             Set<ID> ProjectLIids = new Set<ID>();
             Set<String> Prod2Name = new Set<String>();
             Map<ID,Project_LI__c> projLImap = new Map<ID,Project_LI__c>();
             Map<ID,Product2> prod2Map = new Map<ID,Product2>();
             Map<ID,Attachment> attMap = new Map<ID,Attachment>();


             for(Project_LI__c newAddedLI:addedLI){
             	ProjectLIids.add(newAddedLI.Id);
             }

             if(!ProjectLIids.isEmpty()){
             	Project_LI__c ddd= new Map<ID,Project_LI__c>([Select id,name,LI_Name__c,Project_Name__c from Project_LI__c where id in :newAddedLI.Id]);
             	for(Project_LI__c pli :ddd.values()){
         			Prod2Name.add(pli.LI_Name__c)
             	}
             	prod2Map = newMap<Id,Product2>([[Select id,name from Product2 where name in :Prod2Name]);
             	
             	attMap = new Map<Id,Attachment>([Select Id,name,body,ParentId from Attachment where ParentId in :prod2Map.keySet()]);
             }

             
	         for(Project_LI__c newAddedLI:addedLI){   // In this for Loop I have written 3 SOQL queries. How can I write these 3 SOQL s to Out side?
	                    
	            Project_LI__c getPLI = ddd.get(newAddedLI.Id);
	            if(getPLI != null){
	            	Product2 oppProd = prod2Map.get(getPLI.LI_Name__c);
	            	if(oppProd != null){
	            		for(Attachment aa :attMap.values()){
	            			if(aa.ParentId == oppProd.Id){
	            				Attachment a=new Attachment();
	                            a.name=aa.name;
	                            a.body=aa.body;
	                            a.ParentId=newAddedLI.id;
	                            newAList.add(a);
	            			}
	            		}
	            	}
	            }
	            
             }
             insert newAList;
         }
    }
}
yarramyarram
Thanks Jigar & John,
--------->  Jigar ----thank you very much. your solution is working me without limit exceed error. i need one more help from your side can you please                          help me the  test class for code coverage of your solution.
John ---- thanks for your reply--your solution also working but attachments are not get inserted into the Project LI Records. 

Jigar please help me on test class.
Thanks,
Yarram
Jigar.LakhaniJigar.Lakhani
Hi Yarram,

Below is test class for the code.
 
@isTest
private class OLIUtilTest {

	static testMethod void createProjectTest() {
		
		Account objAccount = new Account();
        objAccount.Name = 'Test Account';
        Insert objAccount;
        
        Product2 objProduct = new Product2();
        objProduct.Name = 'Test Product';
        objProduct.Description = 'Test Product';
        Insert objProduct;
        
		Attachment objAttachment = new Attachment();   	
    	objAttachment.Name='Unit Test Attachment';
    	Blob objBlobBody = Blob.valueOf('Test Attachment Body');
    	objAttachment.body = objBlobBody;
        objAttachment.parentId = objProduct.id;
        Insert objAttachment;
		
        Opportunity objOpportunity = new Opportunity();
        objOpportunity.AccountId = objAccount.Id;
        objOpportunity.Name = 'Test Opportunity';
        objOpportunity.StageName = 'Closed Won';
        objOpportunity.CloseDate = system.Today();
        Insert objOpportunity;
		
		PriceBook2 objPricebook = [select Id from Pricebook2 where isStandard=true];
        PricebookEntry objPriceBookEntry = new PricebookEntry(Pricebook2Id = objPricebook.Id, Product2Id=objProduct.Id,UnitPrice=500,IsActive=true);
        Insert objPriceBookEntry;
		
		OpportunityLineItem objOptyLineItem = new OpportunityLineItem();
        objOptyLineItem.PriceBookEntryId = objPriceBookEntry.Id;
        objOptyLineItem.OpportunityId = objOpportunity.Id;
        objOptyLineItem.Quantity = 1;
        objOptyLineItem.Unitprice = 500;
        Insert objOptyLineItem;
		
		OLIUtil.createProject(String.ValueOf(objOpportunity.Id));
	}

}

Thanks and Cheers,
Jigar
yarramyarram
HI Jigar,

thank you very much for  giving the test class also, i will check this.

I have one more issue on my below trigger. Please help me on this.

Now i am getting the same Too many soql queries error on my below trigger when i give the quatity as 100 then below trigger will break down the OLIs as 100 records based on Quantity value and get insert 100 OLI records into the Opp.LineItem object.

 HOW can I write the SOQL query OUTSide the For LOOP as per my requirement. Below is my trigger.

trigger QuantityBreakDownTrigger on OpportunityLineItem (before Insert) {
    if(Trigger.isBefore){

     if (trigger.isInsert) {
            List<OpportunityLineItem> opplitemList=new List<OpportunityLineItem>();
            OpportunityLineItem oli;
            OpportunityLineItem newOLI1;
                
        
        for(OpportunityLineItem NewOppLineItem:Trigger.new){// // In this for Loop I have written only one SOQL query(for Opportunity record).                                                                                                      
                     
            opportunity newOpp=[Select id,name,stageName,Amount,CloseDate,Sample_Receipt__c,Study_Number__c, from Opportunity                                                    where Id=:NewOppLineItem.OpportunityId];////////////HOw can I write this 1 SOQL to Out side?
                    
           if(NewOppLineItem.Quantity>1){
                        
            for(Integer i=1; i<=NewOppLineItem.Quantity; i++){
                            OpportunityLineItem newOLI= new OpportunityLineItem ();
                                newOLI.ServiceDate=NewOppLineItem.ServiceDate;
                                newOLI.OpportunityId=newOpp.Id;
                                newOLI.PricebookEntryId=NewOppLineItem.PricebookEntryId;
                                newOLI.Quantity=1;
                                 newOLI.UnitPrice=NewOppLineItem.UnitPrice;
                                newOLI.TotalPrice=NewOppLineItem.TotalPrice;
                                newOLI.Description=NewOppLineItem.Description;
                                newOLI.Discount=NewOppLineItem.Discount;
                                newOLI.Study_Number__c=newOpp.Study_Number__c;
                                newOLI.Sample_Receipt__c=newOpp.Sample_Receipt__c;
                                        opplitemList.add(newOLI);
                        }
                    }
                    else{
                                NewOppLineItem.Study_Number__c=newOpp.Study_Number__c;
                                NewOppLineItem.Sample_Receipt__c=newOpp.Sample_Receipt__c;
                    }
                }
            insert opplitemList;
        }

    }

}

Thanks,
Yarram.
Jigar.LakhaniJigar.Lakhani
Hello,

Below is the redesigned code for your trigger. You should use "After Insert" trigger for your requirement which is more secure the "Before Insert".

Apex Trigger:
trigger QuantityBreakDownTrigger on OpportunityLineItem (AFTER INSERT) {
	if (Trigger.IsAfter && Trigger.IsInsert) {
		
		Set<Id> setOpportunityId = new Set<Id>();
		for (OpportunityLineItem objOLI:Trigger.new) {
			if (objOLI.OpportunityId != null) {
				setOpportunityId.Add(objOLI.OpportunityId);
			}
		}
		
		Map<Id,Opportunity> mapIdOpportunity = new Map<Id,Opportunity>([SELECT Id,Name,StageName,Amount,CloseDate,Sample_Receipt__c,Study_Number__c 
																		FROM Opportunity WHERE Id In:setOpportunityId]);
		
		List<OpportunityLineItem> listInsertOptyLineItems = new List<OpportunityLineItem>();
		
		for (OpportunityLineItem objOLI:Trigger.new) {
			if (objOLI.Quantity != null && objOLI.Quantity > 1) {
				for(Integer intI=1; intI <= objOLI.Quantity; i++){
					if (mapIdOpportunity != null && mapIdOpportunity.size() > 0 && mapIdOpportunity.Get(objOLI.OpportunityId) != null) {
					
						Opportunity objOpportunity = mapIdOpportunity.Get(objOLI.OpportunityId);
						
						OpportunityLineItem objNewOLI = new OpportunityLineItem();
						objNewOLI.Quantity = 1;
						objNewOLI.ServiceDate = objOLI.ServiceDate;
						objNewOLI.OpportunityId = objOpportunity.Id;
						objNewOLI.PricebookEntryId = objOLI.PricebookEntryId;
						objNewOLI.UnitPrice = objOLI.UnitPrice;
						objNewOLI.TotalPrice = objOLI.TotalPrice;
						objNewOLI.Description = objOLI.Description;
						objNewOLI.Discount = objOLI.Discount;
						objNewOLI.Study_Number__c = objOpportunity.Study_Number__c;
						objNewOLI.Sample_Receipt__c = objOpportunity.Sample_Receipt__c;
						listInsertOptyLineItems.Add(objNewOLI);
					}
				}
			}
		}
		
		if (listInsertOptyLineItems != null && listInsertOptyLineItems.size() > 0) {
			Insert listInsertOptyLineItems;
		}
	}
}

Thanks and Cheers,
Jigar
Jigar.LakhaniJigar.Lakhani
Sorry, forget about else condition for quanitity. Because of this else condition of quantity you must need to use "Before Insert".
Here latest code for opportuinty line item trigger.

Apex Trigger:
trigger QuantityBreakDownTrigger on OpportunityLineItem (BEFORE INSERT) {
	if (Trigger.IsBefore && Trigger.IsInsert) {
		
		Set<Id> setOpportunityId = new Set<Id>();
		for (OpportunityLineItem objOLI:Trigger.new) {
			if (objOLI.OpportunityId != null) {
				setOpportunityId.Add(objOLI.OpportunityId);
			}
		}
		
		Map<Id,Opportunity> mapIdOpportunity = new Map<Id,Opportunity>([SELECT Id,Name,StageName,Amount,CloseDate,Sample_Receipt__c,Study_Number__c 
																		FROM Opportunity WHERE Id In:setOpportunityId]);
		
		List<OpportunityLineItem> listInsertOptyLineItems = new List<OpportunityLineItem>();
		
		for (OpportunityLineItem objOLI:Trigger.new) {
			
			Opportunity objOpportunity = mapIdOpportunity.Get(objOLI.OpportunityId);
			
			if (mapIdOpportunity != null && mapIdOpportunity.size() > 0 && mapIdOpportunity.Get(objOLI.OpportunityId) != null) {
				if (objOLI.Quantity > 1) {
					for(Integer intI=1; intI <= objOLI.Quantity; i++){
						OpportunityLineItem objNewOLI = new OpportunityLineItem();
						objNewOLI.Quantity = 1;
						objNewOLI.ServiceDate = objOLI.ServiceDate;
						objNewOLI.OpportunityId = objOpportunity.Id;
						objNewOLI.PricebookEntryId = objOLI.PricebookEntryId;
						objNewOLI.UnitPrice = objOLI.UnitPrice;
						objNewOLI.TotalPrice = objOLI.TotalPrice;
						objNewOLI.Description = objOLI.Description;
						objNewOLI.Discount = objOLI.Discount;
						objNewOLI.Study_Number__c = objOpportunity.Study_Number__c;
						objNewOLI.Sample_Receipt__c = objOpportunity.Sample_Receipt__c;
						listInsertOptyLineItems.Add(objNewOLI);
					}
				} else {
					objOLI.Study_Number__c = objOpportunity.Study_Number__c;
					objOLI.Sample_Receipt__c = objOpportunity.Sample_Receipt__c;
				}
			}
		}
		
		if (listInsertOptyLineItems != null && listInsertOptyLineItems.size() > 0) {
			Insert listInsertOptyLineItems;
		}
	}
}

Thanks and Cheers,
Jigar

 
This was selected as the best answer
yarramyarram
Hi Jigar,

Thank you very much. trigger is working...

Thanks,
Yarram.
yarramyarram
Hi Jigar,

today i tried to implement the test class for  OLIUtil (77% coverd) class as you mentioned the above test class.
 
i am not able to covers the Attachments code (line no : 63 to 72 if condition part) part  and line no :50 to 57 part by usign your test class.

HOw can i achive/covers that if condition statements code?

please help me the code coverage part.

Thanks,
Yarram. 

 
Jigar.LakhaniJigar.Lakhani
Hello,

I am not able to find any other issue to cover those lines, based on my thinking it should cover these lines. but as you are saying that it is not covered need to put debug points and try to see that what is going on.

For debugging that code if you can send me your organization credential or if you have any other developer organization where you can create these apex class and test class, it would be great to have a look over there.

What would you prefere ?

Thanks and Cheers,
Jigar
yarramyarram
Hi Jigar,

thannks for your reply, i have cross checked my test class code and finally it was covered 100%.

thanks alot Jigar.

Thanks,
Yarram.
yarramyarram
HI jigar,
i am getting too many soql limt exceed error when my below class executed. Please help me on this. This is very urgent.

public class ProjectUtil{  
   
    public static void beUpdate(List<OpportunityLineItem > listOLIList){
        Project_Assay__c PrjAssay;
for (OpportunityLineItem OppLI: listOLIList) {
            
  PrjAssay=[select id,name,Assay_Name__c,Assay_Number__c,Invoice_Issue_Date__c,Invoice_Number__c,Lab_End_Date__c,Sales_Price__c,
                                       Invoice_Amount_Payable__c,Lab_Start_Date__c,OppAssay__c,Project_Name__c,Quantity__c,Report_Date__c,Study_Number__c
                                    from Project_Assay__c where OppAssay__c=:OppLI.OppAssay__c];                   
                           
                            PrjAssay.Assay_Number__c=OppLI.ProductCode;
                            PrjAssay.Quantity__c=OppLI.Quantity;
                            PrjAssay.Study_Number__c=OppLI.Study_Number__c;
                            PrjAssay.OppAssay__c=OppLI.OppAssay__c;
                            PrjAssay.Sales_Price__c=OppLI.UnitPrice;
                            
        }
         update PrjAssay;
    
    }
    
}