0

I am in need of looping through three Objects: Opportunity, OpportunityContactRoles, and a custom object we will call CR__c.

My question is: is it better to write a Wrapper class for these three objects, add the data to a list, and then loop through the list OR if I can do something like this:

for (Opportunity o : SELECT AccountId FROM Opportunity WHERE Id IN :oppStatementsIn) {
    for(CR__c cr : SELECT Id, Opportunity__r.AccountId FROM CR__c WHERE Opportunity__c = o.Id){
        for(OpportunityContactRole ocr : SELECT Id, ContactId, Role FROM OpportunityContactRole WHERE OpportunityId = o.Id){

When I look at this my immediate reaction is a hard no because of queries inside of for loops. But I am not sure if because of it's structure it is an acceptable way of formatting it. Additionally, there will only ever be 1 - 3 Opportunities to kick off this logic.

Any thoughts on this structure?

2

Three nested for loops are not a problem per se. The issue is how these loops have a multiplicative effect on limits consumption by either dramatically increasing the computational complexity of your code or by inefficiently performing SOQL and DML.

The latter is the issue you have here, but it looks like it's pretty easy to fix because you're working with child objects of Opportunity.

for (Opportunity o : [SELECT AccountId, (SELECT Id, ContactId, Role  FROM OpportunityContactRoles) WHERE Id IN :oppStatementsIn]) {

You just use subqueries on the parent query to get the child data, instead of looping a query.

Then your innermost loop becomes

    for (OpportunityContactRole ocr : o.OpportunityContactRoles) {

I don't want to say for sure that these parent-child queries are the right solution for you, though, because it looks strange to me that you're nesting loops on two different child objects of Opportunity. This does have a multiplicative complexity cost (you're basically performing a full join manually, evaluating every combination of CR__c and OpportunityContactRole), and it's not clear why or whether that's what you want to do.

You may benefit from reading through SOQL Relationship Queries, because it could be that your objective is better served by a different query structure entirely.

|improve this answer|||||
  • ah much smarter. You are right, I can do all three in one nested query. I will read through the documentation though just in case there is a better way, but I think this will work perfectly for what I am trying to do. Thank you! – Olivia Apr 3 '18 at 22:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.