Multiple Sub Query using child parent relationship

Question

Consider in my basket, I have a package (Main Product-just a holder) which will have 2 standalone products (child) and again the standalone products will have child products (Grand Parent). All these products are from same object called Product Configuration.

We have a child relationship to itself(Parent Configuration) using a custom lookup field. The problem here is, I could only fetch details only up to 1 level (i.e.,package and child details) and I was not able to drill deeper to grand child products.

Query I used to get 1 level of relation ships is below:

SELECT id, name, Access_Type__c, Circuit_Bandwidth_Value__c, QuoteType__c,
    Upstream_Speed_Value__c, IP_Address_Type__c, Care_Level__c, Base_Plan_Value__c, Site_Address__c, 
    cscfga__Quantity__c, Quantity_of_Ip_Address__c, cscfga__Contract_Term__c, Connection_Price__c, 
    Monthly_Rental_Conga__c, Total_Annual_Rental__c, 
    (
        SELECT id, name,cscfga__Product_Family__c,Upstream_Speed_Value__c, cscfga__Quantity__c, Quantity_of_Ip_Address__c, 
            Connection_Price__c, Monthly_Rental_Conga__c,cscfga__Parent_Configuration__r.Circuit_Bandwidth_Value__c,Circuit_Bandwidth_Value__c 
        FROM cscfga__Related_Configurations__r
        WHERE cscfga__Parent_Configuration__c <> '' 
        ORDER BY name
    ) 
FROM cscfga__Product_Configuration__c 
WHERE cscfga__Product_Basket__r.cscfga__Opportunity__c='{pv0}' 
    AND cscfga__Product_Basket__r.csordtelcoa__Synchronised_with_Opportunity__c=True
    AND cscfga__Parent_Configuration__c = '' 
ORDER BY name

Not sure how to write another sub query to get grand child details. When I tried the below query:

SELECT id, name, Access_Type__c, Circuit_Bandwidth_Value__c, QuoteType__c, Upstream_Speed_Value__c, 
    IP_Address_Type__c, Care_Level__c, Base_Plan_Value__c, Site_Address__c, cscfga__Quantity__c, 
    Quantity_of_Ip_Address__c, cscfga__Contract_Term__c, Connection_Price__c, 
    Monthly_Rental_Conga__c, Total_Annual_Rental__c,
    (
        SELECT id, name, cscfga__Product_Family__c, Upstream_Speed_Value__c, cscfga__Quantity__c, 
            Quantity_of_Ip_Address__c, Connection_Price__c, Monthly_Rental_Conga__c, cscfga__Parent_Configuration__r.Circuit_Bandwidth_Value__c, Circuit_Bandwidth_Value__c,
        FROM cscfga__Related_Configurations__r
        WHERE cscfga__Parent_Configuration__c <> '' 
        ORDER BY name
    ),
    (
        SELECT id, name, cscfga__Product_Family__c 
        FROM cscfga__Related_Configurations__r
        WHERE cscfga__Parent_Configuration__c <> '' 
        ORDER BY name
    )
FROM cscfga__Product_Configuration__c 
WHERE cscfga__Product_Basket__r.cscfga__Opportunity__c='0066E000002Xrr0' 
    AND cscfga__Product_Basket__r.csordtelcoa__Synchronised_with_Opportunity__c=True 
    AND cscfga__Parent_Configuration__c = '' 
ORDER BY name

I got below error:

MALFORMED_QUERY: from cscfga__Related_Configurations__r ^ ERROR at Row:6:Column:6 Cannot follow the same aggregate relationship twice: cscfga__Related_Configurations__r

Can someone please help here. Note: I am executing these queries in Workbench.

Answer 1

The error message is fairly clear. In SOQL, you cannot have a subquery that uses the same relationship name that another subquery used.

The syntax you used isn't how you'd query a grandchild record, but the right way to do it doesn't matter, because in a single SOQL query you can only query one level down the relationship hierarchy (you can only go from parent to child, you cannot then go from child to grandchild).

This will either require two separate queries, or starting from your grandchild records, and querying up the hierarchy to the child and parent records (you can query up to 5 levels going up a hierarchy).

Given the number of fields you're querying, I'd recommend the two query approach.

A basic example to get you going

// Storing the results of this query in a map isn't strictly necessary, but
//   doing so would be helpful if you wanted to access MyParent__c records from
//   grandchildren.
Map<Id, MyParent__c> myParentMap = new Map<Id, MyParent__c>([SELECT <some fields>, (SELECT Id, <some fields> FROM MyChildren__r) FROM MyParent__c]);

// We need to gather child Ids for the second query
Set<Id> childIds = new Set<Id>();
for(MyParent__c parent :myParentMap.values()){
    for(MyChild__c child :parent.MyChildren__r){
        childIds.add(child.Id);
    }
}

// Storing the results of this query in a map is pretty much required here.
// You could technically store it in a List, but that would result in you wasting
//   a lot of time trying to find which child to use when iterating over Parent
//   records.
// A map really is the most sensible option.
Map<Id, MyChild__c> myChildMap = new Map<Id, MyParent__c>([SELECT Id, (SELECT Id, <some other fields> FROM MyGrandchildren__r) FROM MyChild__c WHERE Id IN :childIds]);

// Iterating over the hierarhcy
// The two query method keeps all records nicely grouped (i.e. when you get to
//   grandchildren, it's guaranteed that you're only getting the grandchildren
//   under the current child and parent records, not records that span
//   different child records or different parent records)
//   without much extra work.
// It would be a fair amount of extra work if you were to use a single query starting
//   from MyGrandchild__c and querying up the hierarchy to get MyChild__c and 
//   MyParent__c data.
for(MyParent__c parent :myParentMap.values()){
    for(MyChild__c child :parent.MyChildren__r){
        // Note how, for this innermost loop, we need to grab the grandchildren
        //   from the map that holds the results of the second query
        for(MyGrandchild__c grandchild :myChildMap.get(child.Id).MyGrandchildren__r){
        }
    }
}