Nesting Parent-Child Relationship Query

Question

In my data structure I have a several custom objects that form a relationship to the Standard Salesforce Account Object.

I have three custom objects:

• L_Account
• L_Contact
• L_Enrollment

L_Enrollment -> L_Contact -> L_Account -> Standard SF Account

I want to get a Count per Standard SF Account for how many L_Enrollment exist that match a creation date (on the L_Enrollment level)

Can this be done? I am familiar with SOQL Relationships but need a great amount of help on the syntax.

Answer 1

You could do it with aggregate queries. Making some assumptions about the API Names of your relationship fields:

Map<Id, Integer> accountIdToEnrollmentCount = new Map<Id, Integer>();
for (AggregateResult aggregate : [
    SELECT count(Id) records, L_Contact__r.L_Account__r.Account__c accountId
    FROM L_Enrollment__c WHERE CreatedDate = :2016-10-21
    GROUP BY L_Contact__r.L_Account__r.Account__c
])
    accountIdToEnrollmentCount.put(
        (Id)aggregate.get('accountId'),
        (Integer)aggregate.get('records')
    );

---

What's going on here?

The map itself is fairly straightforward. Let's break down the query:

• You can GROUP BY a specific field (even many relationships distant).
  • When you perform queries using this clause, they return List<AggregateResult>.
• When you use this clause, each field must be grouped or aggregated.
  • With the relationship to Account you group it by including it in the GROUP BY clause.
  • With Id you count the number of populated values, which gives you the total number of enrollments you are looking for.
• When you use this clause, you can alias any field in your SELECT clause, which can make your code a lot more concise and easier to wrap your head around!
  • Here we can now just call aggregate.get('accountId') even though the relationship is really L_Contact__r.L_Account__r.Account__c.
  • We can also get the count in an easy to understand way. Without aliasing, you would have to use aggregate.get('expr0'), which is ugly and doesn't scale very well.
• Note that when working with AggregateResult, you have to use the SObject.get method, which returns an un-cast Object. That's why you have to cast the results ((Id), (Integer)).