How can I count the number of details in master-detail relationship?

Let us suppose I have Detail_Type__c and Master_Type__c.

Now if I have and Id of a Master_Type__c record I can do the following query:

List<Detail_Type__c> allDetails = [
 FROM Detail_Type__c
 WHERE Master_Type__c = :Id
Integer res = allDetails.size();

But I would like to be able to somehow do the query of the following form:

List<Master_Type__c> allDetails = [
 SELECT Master_Type_Field1__c, Master_Type_Field2__c, (SELECT NumberOf Detail_Type__r)
 FROM Detail_Type__c
 WHERE Master_Type_FieldDoesNotMatter_c = :something

In other words I would like to use something like an aggregate function to count the number of details in master-detail relationship for each detail in a list of details.

How can I do it?

  • I didn't understand, why standard roll-up summary field is not applicable here ? – Oleksandr Berehovskyi Oct 15 '18 at 10:45
  • @OleksandrBerehovskiy, thank you for the idea about roll-up summary field. It would definitely solve the issue, but in a team I can not change data model, I can only use it. – Yaroslav Trofimov Oct 15 '18 at 10:56
  • 2
    Adding a new field won't be count as modding the data model. The roll-up summary will be more effective than querry plus the querry can timeout but the rollup field will not. – Pranay Jaiswal Oct 15 '18 at 11:32

use count aggregate function. You can have Set<Id> masterIds = ... in this case you can use in SOQL operator.

List<AggregateResult> result = [
    select count(Id)cnt, Master_Type__c
    from Detail_Type__c
    where Master_Type__c in :masterIds
    group by Master_Type__c

for (AggregateResult ar : result)  {
    System.debug('Master_Type__c' + ar.get('Master_Type__c'));
    System.debug('Count' + ar.get('cnt'));
  • 2
    Using cnt isn't the best abbreviation out there. I might recommend records or childCount or some such. – Adrian Larson Oct 15 '18 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.