You need to sign in to do that
Don't have an account?
Ross 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:
Here's the full code with that in it:
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.
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.
https://developer.salesforce.com/page/Apex_Code_Best_Practices
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