SOQL Sub-Query on child of child

Question

I'm having trouble figuring out if this is actually possible as nothing I've tried so far works.

I have a large query on OrderItems (pared down here for simplicity) that needs information from a custom object Opportunity_Partners__r which has a Master-Detail relationship to Opportunity.

This is being queried from an external system that requires all of the data returned in a single query, so hoping that this is achievable.

SELECT 
Id,
Order.Id, 
Order.Opportunity.Id, 
Order.Account.Name, 
(SELECT Partner_Name__c FROM Opportunity_Partners__r WHERE role__c = 'PIP' AND Opportunity__c = Order.Opportunity)
FROM OrderItem
WHERE Order.EffectiveDate >= LAST_N_DAYS:365 AND
Order.Opportunity_Record_Type__c  != 'Renewal' 

Answer 1

We're only allowed to traverse one level down the object hierarchy from the object used in the outer query. We also are not allowed to compare two fields to one another (the right-hand side always needs to be a value) So your query, as written, is impossible.

With your pared-down query, it doesn't appear that you are doing anything that requires your query to be based on OrderItem though. If you make your outer query based on Order instead, you could get this to work by using two parent-child queries.

SELECT 
    Id, Opportunity.Id, Account.Name, 
    (SELECT Id FROM OrderItems),
    (SELECT Partner_Name__c FROM Opportunity_Partners__r WHERE role__c = 'PIP')
FROM Order
WHERE 
    EffectiveDate >= LAST_N_DAYS:365 AND
    Opportunity_Record_Type__c  != 'Renewal' 

If your full query allows you to do something like that, you may still have issues with how the data is returned. It's true that your query will return both Opportunity_Partner__c and OrderItem, but if you pick out a single OrderItem, you wouldn't be able to do something like myOrderItem.Order.Opportunity_Partners__r.

If that's what you need to provide in the end, then you'd need to do some extra processing. Child records are stored as an embedded List<SObject> in the 'parent' record, and parent records are stored as an embedded SObject in the 'child' record. We can set the parent object reference. We can't set the child collection reference (we can call .add() on it, but this appears to have no effect).

So given my example query, we can make executing myOrderItem.Order.Opportunity_Partners__r possible by setting the Order parent reference.

List<OrderItem> orderItemList = new List<OrderItem>();

for(Order ord :[SELECT 
    Id, Opportunity.Id, Account.Name, 
    (SELECT Id FROM OrderItems),
    (SELECT Partner_Name__c FROM Opportunity_Partners__r WHERE role__c = 'PIP')
FROM Order
WHERE 
    EffectiveDate >= LAST_N_DAYS:365 AND
    Opportunity_Record_Type__c  != 'Renewal']){
    for(OrderItem ordItem :ord.OrderItems){
        ordItem.Order = ord;
        orderItemList.add(ordItem);
    }
}

// Just for demonstration
// In reality, you'd probably just "return orderItemList;"
for(OrderItem ordItem :orderItemList){
    system.debug(ordItem.Order.Opportunity_Partners__r);
}

That approach also means that you aren't forced to do this in a single query. As long as you have one query that can cause the child record collection to be populated, you can cause another child record to be able to traverse up (to the parent) then down (to the cousin records, I guess I'd call them). If you're able to return JSON instead of SObject records, then you wouldn't even need a common parent.

ex.

Map<String, Object> unserialized = new List<Map<String, Object>>{
    new Map<String, Object>{
        'Id' => orderItemList[0].Id,
        'Assets' => new List<asset>(accountQueryResult.Assets)
    }
};
system.debug(JSON.serialize(unserialized));

About the only thing I'd keep in mind is that setting the parent reference (or tacking it on to some JSON) is that it'll increase the size of the response to this external system you mention (because the Opportunity and Opportunity_Partner__c records will be duplicated for OrderItems on the same Order).