Nested For Loop and SOQL Best Practice

Question

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?

Answer 1

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.