SOQL subquery access variables

Question

Attempting to access the variables MIlestone1_Project__c has a Lookup relationship to Account with relationship name: `Projects__r'.

List<Account> accList = [
    SELECT Id, Name, EHR_Status__c, PM_Status__c, Project_Imp_Status__c, Other_Status__c, (
        SELECTClient_Advisor_Email__c, Resource_Coordinator_Email__c
        from Projects__r
    ) 
    FROM Account
    WHERE Id IN :AcctIds
];

List<String> emailAdds = new List<String>();
for (Account al: accList) {
    emailAdds.add(al.Client_Advisor_Email__c);
    emailAdds.add(al.Resource_Coordinator_Email__c); 
}

I get the error message:

Variable does not exist: Client_Advisor_Email__c

Why do I get this error? How can I fix it?

Answer 1

You have to loop through your subquery list of records. Here is how you would access these fields and records:

for(Account a : accList)
{
    for(Project__c p : a.Projects__r)
    {
        System.debug(p.Client_Advisor_Email__c);
    }
}

Answer 2

Some background

When working with data that's related to an object, there's two ways it can be related:

• Child-to-Parent (e.g. Opportunity.Amount in a query on OpportunityLineItem)
• Parent-to-Child (e.g. (SELECT Id FROM Opportunities) in the SELECT clause in a query on Account)

An important concept to keep in mind here is that all relationship fields in Salesforce are 1:n (one-to-many). Given a single relationship field, for example, OpportunityId on OpportunityLineItem, that field can only hold a single value.
OpportunityLineItem, the child record, can only be related to a single Opportunity, the parent record.

If we switch our point of view to the Opportunity, a single parent (Opportunity) can have multiple children (OpportunityLineItems).

How related data is stored/represented in the query result

In the end, every record we query data for is presented to us as an SObject

If your query looks like this:

[SELECT Id, Account.Name, (SELECT Id, UnitPrice FROM OpportunityLineItems) 
FROM Opportunity 
LIMIT 1]

Then we're querying data from Account, Opportunity, and OpportunityLineItem. All of those are represented as SObject. This allows us to do things like Account myAccount = myOpp.Account;

What happens with the parent-to-child subquery results then?

Since a parent can have many children, we can't store all of the child records in a single SObject.

If Salesforce automatically chose one child to put in an SObject for us, which one would it be? How would we switch to another child record?

I hope that this is starting to make your code-senses tingle. We want a single variable to hold multiple pieces of data. What tool(s) do we have to accomodate that?

Salesforce's solution to this is to return child records as a List<SObject>. This allows us to do things like List<OpportunityLineItem> myOLIs = myOpp.OpportunityLineItems;

Now, to your issue

Your code was assuming that the child records were a single SObject (glommed onto the Account object) instead of being an honest-to-goodness List<SObject> of its own.

Some common patterns to work with records from a subquery include:

Looping over the subquery results (the safest method)

for(Account a : accList){
         // a.Projects__r is a List, and we're feeding it into a loop
         //   just like we would with any other List
         for(Project__c p : a.Projects__r){
             System.debug(p.Client_Advisor_Email__c);
         }
     }

Accessing a single record from the subquery

// It's just like accessing an index of any other List because _it is just a List_
for(Account a : accList){
    System.debug(a.Projects__r[0].Client_Advisor_Email__c);
}

Salesforce prefers we use the nested loop approach because, after a certain number of child records, there's an internal call to queryMore() which a loop can handle correctly (and basically nothing else can handle it properly). Even calling a.Projects__r.size() runs the risk of needing a queryMore().