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()
.