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