+ Start a Discussion
Ross Gilbert 31Ross Gilbert 31 

too many soql queries on opportunity trigger

I've got a method called from the after update of my opportunity trigger.  I'll paste it below.  Somewhere in here there's a too many soql queries error I get when updating a particular opportunity.  The debug log shows the error at the line with this map:

 
Map<Id, Account> accMap = new Map<Id, Account>([SELECT Id, (SELECT Term_Begin_Date__c, Term_End_Date__c, Finance_Grace_Period_Date__c FROM Customer_Assets__r 
                                            WHERE Term_End_Date__c != null ORDER BY Term_End_Date__c DESC) 
                                        FROM Account WHERE Id IN: accIds]);





Here's the full code with that in it:
 
public static void createUpdateAssets(Map<ID, OpportunityLineItem> qualifiedOLIsMap){
        if(qualifiedOLIsMap.size() > 0){
          //get all the product ids from the OLIs
          Set<Id> prodIds = new Set<Id>();
          Set<Id> contractIds = new Set<Id>();
          Set<Id> accIds = new Set<Id>();
          Opportunity thisOpp;
          for(OpportunityLineItem oli : qualifiedOLIsMap.values()){
            if(thisOpp == null){
                thisOpp = oli.Opportunity;
            }
            if(oli.PriceBookEntry.Product2Id != null){
              prodIds.add(oli.PriceBookEntry.Product2Id);
            }
            if(oli.Opportunity.Contract__c != null){
              contractIds.add(oli.Opportunity.Contract__c);
            }
            if(oli.Opportunity.Contract__r.MLSA__c != null){
              contractIds.add(oli.Opportunity.Contract__r.MLSA__c);
            }
            accIds.add(oli.Opportunity.AccountId);
          }
          
          Map<Id, Product2> prodMap = new Map<Id, Product2>([SELECT Id, (SELECT Id, Name FROM Versions_Product_Junctions__r)
                                      FROM Product2 WHERE Id IN: prodIds]);
          
          //added Term_Date__c to SOQL query
          Opportunity test = [SELECT Id, RecordTypeId FROM Opportunity WHERE Id =: thisOpp.Id];
          Map<Id, Contract> contractMap = new Map<Id, Contract>([SELECT Id, StartDate, EndDate, Term_Date__c,
                                          (SELECT Term_Begin_Date__c, Term_End_Date__c, Finance_Grace_Period_Date__c FROM Taxware_Assets__r 
                                            WHERE Term_End_Date__c != null ORDER BY Term_End_Date__c DESC) 
                                        FROM Contract WHERE Id IN: contractIds]);
            
          for(Contact_Contract_Junction__c ccj : [SELECT Contact__c, Contract__c FROM Contact_Contract_Junction__c WHERE Contract__c IN: contractIds]){
            Set<Id> contactIds = contractContactMap.get(ccj.Contract__c);
            if(contactIds == null){
              contactIds = new Set<Id>(); 
            }
            contactIds.add(ccj.Contact__c);
            contractContactMap.put(ccj.Contract__c, contactIds);
          }
            
            List<OLIAssetWrapper> allOLIAssets = getOLIAssets(qualifiedOLIsMap, prodMap, contractMap);
            
            List<Customer_Asset__c> allAssetsToInsert = new List<Customer_Asset__c>();
            List<Customer_Asset__c> allAssetsToUpdate = new List<Customer_Asset__c>();
            Customer_Asset__c thisOLIAsset;
            for(OLIAssetWrapper oliAst: allOLIAssets){
                thisOLIasset = oliAst.ast;
                if(thisOLIasset.Id == null){
                    allAssetsToInsert.add(thisOLIasset);
                }else{
                    allAssetsToUpdate.add(thisOLIasset);
                }
            }
            
            update allAssetsToUpdate;
            //IR-1357
            insert allAssetsToInsert;
            Set<Id> newAssetIds = new Set<Id>();
            for(Customer_Asset__c thisAsset : allAssetsToInsert){
                newAssetIds.add(thisAsset.Id);
            }
            List<Customer_Asset__c> newAssets = new List<Customer_Asset__c>();
            newAssets = [SELECT Id, Account__c FROM Customer_Asset__c WHERE Id =: newAssetIds];
            if(test.RecordTypeId != '012f0000000D9H9'){
                List<Contract_Asset_Connection__c> conns = new List<Contract_Asset_Connection__c>();
                Contract_Asset_Connection__c thisConn;
                for(Customer_Asset__c thisAsset : newAssets){
                    thisConn = new Contract_Asset_Connection__c();
                    thisConn.Contract__c = thisOpp.Contract__c;
                    thisConn.Asset__c = thisAsset.Id;
                 thisConn.Account__c = thisAsset.Account__c;
                  if(thisConn != null)
                    conns.add(thisConn);
                }
                if(!conns.isEmpty() && test.RecordTypeId != '012i0000001AgIY' && test.RecordTypeId != '012i000000129Hi' && test.RecordTypeId != '012i0000001AgId'){
                    insert conns;
                }
            }   
            Map<Id, Account> accMap = new Map<Id, Account>([SELECT Id, (SELECT Term_Begin_Date__c, Term_End_Date__c, Finance_Grace_Period_Date__c FROM Customer_Assets__r 
                                            WHERE Term_End_Date__c != null ORDER BY Term_End_Date__c DESC) 
                                        FROM Account WHERE Id IN: accIds]);
            
            Map<Id, Entitlement> entMap = new Map<Id, Entitlement>();
            for(OpportunityLineItem oli: qualifiedOLIsMap.values()){
            //if it's an update and the order type is renewal, update the Entitlement dates
                if(oli.Opportunity.Order_Type__c == Constants.ORDER_TYPE_RENEWAL || oli.Opportunity.Order_Type__c == Constants.ORDER_TYPE_ADD_ON || oli.Opportunity.Division__c == 'Taxify'){
                  if(oli.Opportunity.Entitlement__c != null && !entMap.containsKey(oli.Opportunity.Entitlement__c)){
                    Entitlement e = new Entitlement(Id = oli.Opportunity.Entitlement__c);
                    Date oldEntStart = oli.Opportunity.Entitlement__r.StartDate;
                    
                    if(oli.Opportunity.Contract_Paper_Type__c == 'New Paper'){
                        if(oldEntStart != null){//e.StartDate == null
                            e.StartDate = oldEntStart;
                        }else{
                            e.StartDate = oli.Opportunity.Contract__r.StartDate;
                        }
                        //changed EndDate to Term_Date
                        //e.EndDate = oli.Opportunity.Contract__r.Term_Date__c;
                        Account acc = accMap.get(oli.Opportunity.AccountId);
                        Date furthest = Date.today().addYears(-1);
                        if(acc != null && acc.Customer_Assets__r != null && acc.Customer_Assets__r.size() > 0){
                            for(Customer_Asset__c ass : acc.Customer_Assets__r){
                                if(ass.Term_End_Date__c > furthest){
                                    furthest = ass.Term_End_Date__c;
                                }
                            }
                            if(furthest > Date.today()){
                                e.EndDate = furthest;
                            }
                        }
                    }else if(oli.Opportunity.Contract_Paper_Type__c == 'Old Paper'){
                        Account acc = accMap.get(oli.Opportunity.AccountId);
                        Date furthest = Date.today().addYears(-1);
                        if(acc != null && acc.Customer_Assets__r != null && acc.Customer_Assets__r.size() > 0){
                            if(oldEntStart != null){//e.StartDate == null
                                e.StartDate = oldEntStart;
                            }else{
                                e.StartDate = oli.Opportunity.Contract__r.StartDate;
                            }
                            for(Customer_Asset__c ass : acc.Customer_Assets__r){
                                if(ass.Term_End_Date__c > furthest){
                                    furthest = ass.Term_End_Date__c;
                                }
                            }
                            if(furthest > Date.today()){
                                e.EndDate = furthest;
                            }
                        }
                    }
                    //check if finance grace period applies, update entitlement end date
                    Date latestGraceDate = null;
                    Customer_Asset__c thisAsset;
                    Account thisAcc = accMap.get(oli.Opportunity.AccountId);
                    integer numAssets = thisAcc.Customer_Assets__r.size();
                    for(integer i = 0; i < numAssets; i++){
                        thisAsset = thisAcc.Customer_Assets__r.get(i);
                        if(thisAsset.Finance_Grace_Period_Date__c > latestGraceDate){
                            latestGraceDate = thisAsset.Finance_Grace_Period_Date__c;
                        }
                    }
                    if(latestGraceDate > e.endDate){
                        e.endDate = latestGraceDate;
                    }
                    entMap.put(e.Id, e);
                  }
                }
            }
            update entMap.values();
            
            
            List<OpportunityLineItem> allOLIsToUpdate = new List<OpportunityLineItem>();
            Set<ID> opptyIDs = new Set<ID>();
            for(OLIAssetWrapper oliAst: allOLIAssets){
                OpportunityLineItem oli = oliAst.oli;
                oli.Asset__c = oliAst.ast.id;
                
                allOLIsToUpdate.add(oli);
                
                opptyIDs.add(oli.OpportunityID);
            }
            update allOLIsToUpdate;
            
            List<Opportunity> allOpptysToUpdate = new List<Opportunity>();
            for(ID opptyid : opptyIDs){
                Opportunity oppty = new Opportunity(id = opptyid);
                oppty.Fulfillment_Date__c = Date.Today();
                allOpptysToUpdate.add(oppty);
            }
            update allOpptysToUpdate;
        }
    }




I don't see any queries in for loops so I'm not sure how this can be re-written to eliminate the error.  Anyone see anything wrong here?  This is really old code that never really has a problem so this seems to be an edge case/bug in the code, I just can't find it.
Wilfredo Morillo 20Wilfredo Morillo 20
Remember that the limit is shared between all triggers on the same object share the same limits. 

https://developer.salesforce.com/page/Apex_Code_Best_Practices 
Joe BrodarJoe Brodar
Hey Ross,

I took a look through your code, and while you are correct that there are no queries inside of loops, you do use subqueries in a few places. According to this post (https://developer.salesforce.com/forums/?id=906F0000000923aIAA), the limit on these queries is higher, but each of these subqueries also count against your query limit, so that may be causing the issue. Also, I know that I have run into issues with subqueries pulling in more than 50K total rows, which exceeds the query row limit.

Let me know if that helps!

- Joe
Joe BrodarJoe Brodar
Also, just to be sure, there is no loop in the trigger that is calling this static method, correct? If the after update trigger is looping, then all of these queries are technically inside of loops.

- Joe