SOQL to identify the Child records of Parent in salesforce ?

Question

I have Case OWD Private. And its Child object Payment1 and Payment2 has lookup relationship with the Case object.

When Case is submitting that time we're also creating child records of it like Payment1 and Payment2 record.

How to dynamically identify in trigger that particular Case has how many child records ?

Select Id,OwnerId, 
(select Id, OwnerId,CreatedById,Case__c, Name from Payment1__r), 
(select Id,OwnerId,CreatedById,Case__c, Name from Payment_2__r) 
FROM Case 
WHERE Id IN :[List Of Case Ids]

Is there any other way ?

Answer 1

One option, if you have a list of child sObjects for which you need counts, would be to use Dynamic SOQL with a Map of the child sObject names and their relationship fields. It would look something like this (not compiled/tested code):

Set<Id> caseIds; // Assume this is populated.
List<String> childRelationships = new Map<String, String> {
    'Payment1__c' => 'Case__c', 
    // Case__c is the name of the lookup to Case on the Payment1__c object.
    'Payment2__c' => 'Case__c'
};

for (String sObjectName : childRelationships.keySet()) {
    List<AggregateResult> results;
    String relationshipField = childRelationships.get(sObjectName);

    results = Database.query('SELECT count(Id) childObjects, ' 
              + relationshipField + ' caseId FROM ' + sObjectName 
              + ' WHERE ' + relationshipField 
              + ' IN :caseIds GROUP BY ' + relationshipField);

    // Do something with your relationship count data here.
}

Because the relationship fields are indexed and we're querying a minimal column set, this structure should keep performance high and heap consumption low.

While Salesforce best practice is not to execute SOQL in a for loop, this generally means not to execute a query for each object in a list. In this instance, if you want to use dynamic SOQL for your child object queries, you'll need to use a loop and it's not bad practice to do so.