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 = [
 SELECT Id
 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 Berehovskiy Oct 15 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 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 at 11:32
up vote 5 down vote accepted

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 at 14:57

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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