function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Eager-2-LearnEager-2-Learn 

Looping thorugh SOQL Subquery (Nested)

I have searched high and low and cannot find anything on how to loop throught this subquery. The inner FOR loop will not compile.  The new console doesn't tell me what the issue is -- it just shows a red exclamation mark.

 

What I really need to do is search through all opportunties that have a specifiic condition and go get the related contact email and name where the contact is flagged as Email Broker (check box field).  If I could get those results I believe I can use it to build email arrays and send the array off to the email class.

 

Any help is appreciated.

 

 

for ( Contact c : [ SELECT Id, Email, Name, (SELECT OpportunityId
                                                     FROM OpportunityContactRoles ) 
                                   FROM Contact 
                        WHERE Email_Broker_Reminder__c = true ] ) {
                            for ( OpportunityContactRoles detail : c.OpportunityContactRoles ) {
                                
                            }
                            
            //contactsMap.put(c.id, c);
                            
        }

 

Marty Y. ChangMarty Y. Chang

Hello, Tom,

 

There's actually nothing wrong with your subquery.  I believe the problem is a typo in your nested for loop.  You declared detail as OpportunityContactRoles, but it really should be OpportunityContactRole (singular).  The following code compiles just fine and is almost letter for letter what you pasted:

 

for (Contact c :
        [SELECT Id, Email, Name,
                (SELECT OpportunityId
                 FROM OpportunityContactRoles) 
         FROM Contact
         WHERE Email_Broker_Reminder__c = true]) {

    for ( OpportunityContactRole detail : c.OpportunityContactRoles ) {
        
        // Do something...
        
    }   // for each OpportunityContactRole in c.OpportunityContactRoles

    //contactsMap.put(c.id, c);

}   // for each Contact in SOQL query results

 
Eager-2-LearnEager-2-Learn

You are correct  Marty -- thank for your reply.  Actually I was going to repost my original question when I saw that you answered.

 

I have added an additional loop and seem to have a similar problem getting it to compile.  I have tried various plural and singular names with no luck.

 

 

public class EmailBrokerReminder {
    public EmailBrokerReminder() {
        Map<id, contact> contactsMap = new Map<id, contact>();
        for ( Contact c : [ SELECT Id, Email, Name, (SELECT OpportunityId, Opportunity.Email_Broker_On__c
                                                     FROM OpportunityContactRoles ) 
                            FROM Contact 
                            WHERE Email_Broker_Reminder__c = true ] ) {
                            for ( OpportunityContactRole ocr : c.OpportunityContactRoles ) {
                                  System.debug(c.email + ' - ' + c.Name + ' - ' + ocr.id );
                                for ( Opportunity o : c.OpportunityContactRoles.Opportunities ) {
                                    
                                }
                            }
                            
            contactsMap.put(c.id, c);
                            
        }    
    }
}

 
Eager-2-LearnEager-2-Learn

I think I am having a brain freeze!  It shouldn't be another loop.  I should be referencing it as Opportunity.Email_Broker_On__c inside the second loop.  Would you agree?
Marty Y. ChangMarty Y. Chang

I think you're right, Tom.  :-)
Eager-2-LearnEager-2-Learn

Thanks for you support Marty.  I think I am past the hard part now.  I have everything in place as far as looping through the records.  All I have to do is build the emails into an array and send the array to the email class.  I have coded something like that before so I just have to find my old code and use it to get me started.  Thanks again so much.