0

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?

3
  • I didn't understand, why standard roll-up summary field is not applicable here ? Commented Oct 15, 2018 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. Commented Oct 15, 2018 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. Commented Oct 15, 2018 at 11:32

1 Answer 1

5

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'));
}
1
  • 2
    Using cnt isn't the best abbreviation out there. I might recommend records or childCount or some such.
    – Adrian Larson
    Commented Oct 15, 2018 at 14:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .