0

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 ?

  • What is driving you to look for another way? This seems like the natural idiom to me; you cannot, of course, use a Rollup Summary Field in this case. – David Reed Feb 2 '18 at 19:29
  • In future there could be many child objects like Payment-3 or 4... etc, How to make this query dynamic ? I need to keep changing this query in that case ? – Kanika Feb 2 '18 at 19:31
  • 4
    I really question that data model design if this needs to be done. Are your children objects really so different that record types or another mechanism won't allow for management on a single object? – techbusinessman Feb 2 '18 at 21:58
  • As techbusinessman says, using a separate object for each payment is a terrible design. You will not be able to scale this design at all. – David Cheng Feb 3 '18 at 7:31
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.

  • Thanks but cant afford to write query in for loop – Kanika Feb 2 '18 at 19:51

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.