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.
Best Answer
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