function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
SKTSKT 

Issue with Sub-query levels in SOQL

I have a requirement to fetch Parent, Child, and Grandchild and Store them as JSON in the same hierarchy.

Here is my logic:
 
public List<Parent_Project__c> ProjectRecordsList {get; set;}
    ProjectRecordsList = [SELECT Id, Name,Parent_Project__c, (SELECT Id, Name, (select name from Orders__r) FROM Child_Projects__r) FROM Parent_Project__c];
    string strJSON = JSON.serialize(ProjectRecordsList);

Now the issue here is that, when I run this query, I am getting the error as
SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object.

I could able to fetch Parent Project and its child Project successfully whereas when I include Orders (Grandchild), I am seeing this error. I know that I can write queries 5 levels up if I start with Child but in this case, I need to fetch the records starting from Parent to Grandchild.

Can anyone please let me know how to add Orders of Child Projects in the JSON?
AbhinavAbhinav (Salesforce Developers) 
Hi,

There is some limitation around subquery level in Soql.

Pleasse refer below doc:

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_limits.htm#:~:text=A%20custom%20object%20allows%20up,be%20specified%20in%20a%20query.

Thanks!
SKTSKT
@Abhinav,

On that note, if we add Grand Parent and Parent to JSON, is there any way where we can add an extra Grandchild node under Parent Node.
 
public List<Parent_Project__c> ProjectRecordsList {get; set;} 
Public List<Orders__c> Orderslist {get;set;}

ProjectRecordsList = [SELECT Id, Name,Parent_Project__c, (SELECT Id, Name FROM Child_Projects__r) FROM Parent_Project__c]; 

string strJSON = JSON.serialize(ProjectRecordsList);

In the above logic, I have added Parent Project and Its Child Project to JSON. Now, could you please let me know how to add Orders__r of child-parent to the already existing JSON node.

Thank you in advance