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.