Parents Whose Last Created Child is a ‘Failure’

by

I have a lookup relation, so I need to filter Parents (id, Name) whose last created Child has status__c Failure.

Tried something like: 

SELECT Id, Name
FROM Parent
WHERE RecordType.Name = 'rt'
AND Id IN (
  SELECT Parent__c
  FROM Child__c
  WHERE Status__c = 'Failure'
  /*ORDER BY CreatedDate LIMIT 1*/

But I cannot find a way to filter only Parents based on the condition, since you cannot use an ORDER BY in a subquery

Answer

As Adrian noted in the comments on your question, this isn’t possible to achieve via SOQL alone.

While you can’t use ORDER BY in a semi-join/Left Inner Join (i.e. a subquery appearing in the WHERE), you can use ORDER BY in a Left Outer Join (a.k.a. a parent-child subquery, one that appears in the SELECT clause).

I can’t find documentation that corroborates that last statement, but I have the next best thing, code that anyone can run to prove it for themselves. If you feel the need, just run the following as anonymous apex:

for(Account a :[SELECT Id, (SELECT Id FROM Opportunities ORDER BY CreatedDate ASC LIMIT 1) FROM Account LIMIT 10]){
    system.debug(a.Id + ': ' + a.Opportunities);
}

Given that the above works, an approach that I might suggest would be to use a Left Outer Join to find the ‘last created child’ (which I take to mean the most recently created child), and then use apex to figure out whether or not that child record’s status is ‘Failure’

Set<Id> latestChildFailureParentIds = new Set<Id>();

// If you want to find the oldest child record, simply replace 'ORDER BY CreatedDate DESC'
//   with 'ORDER BY CreatedDate ASC'
for(Parent p :[SELECT Id, Name, (SELECT Id, Status FROM Children ORDER BY CreatedDate DESC LIMIT 1) FROM Parent WHERE recordType.Name = 'rt']){
    if(!p.Children.isEmpty() && p.Children[0].Status == 'Failure'){
        latestChildFailureParentIds.add(p.Id);
    }
}

Attribution
Source : Link , Question Author : joralbert , Answer Author : Derek F

Leave a Comment