Problem with - Query Five Levels of Parent-to-Child Relationships in SOQL Queries

Question

I am trying to use this line of Apex code to polulate a List.

I have a root object called Order__c, a child object called OrderDestinations__c and a grand child object called OrderDestinationItems__c. These three have Master Details relationships.

List<Order__c> orderList = [Select Id, (Select Id, (Select Id from OrderDestinationItems__r) from OrderDestinations__r) from Order__c] ;

I am getting the error "SOQL statements cannot query aggregate relationships more than 1 level from the root entity object.

My Salesforce org has API version 60. This article https://help.salesforce.com/s/articleView?id=release-notes.rn_api_soql_5level.htm&release=244&type=5 tells me that my code should work.

Please help me solve this problem.

I tried this code List<Order__c> orderList = [Select Id, (Select Id, (Select Id from OrderDestinationItems__r) from OrderDestinations__r) from Order__c] ;

Answer 1

In API version 60 (prior to Summer '24 release), you have to run this query via SOAP or REST. From the doc:

This ability is limited to SOQL queries via the REST and SOAP query calls on standards and custom objects.

In API version 61 (Summer '24 release), you can do this in Apex. From release notes:

Apex now supports SOQL relationship queries that traverse up to five levels of parent-child records. How: In API version 61.0 and later, SOQL parent-child relationship queries in Apex can contain a parent root as the first level of the query and child relationships up to four levels deep from the parent root.

Consult status.salesforce.com to see when your org is going to support API v61.