+ Start a Discussion
Derhyk Doggett -Derhyk Doggett - 

Use Json Serializer for 3 Levels of Relationship

I'm fairly new the the Json methods available in Apex, and once discovered how easy it is to use, love it, but am struggling with more than 1 level deep of object relation.
Originally I only needed to gather Order and OrderItems, create a Json string, pass it on.
Now a third level has been added where I need to gather a third level object, Order Items Pricing, that are related to the OrderItems.
Since SOQL only allows one additional level for subqueries, I'm struggling with how to gather the Order, OrderItems, Pricing Conditions as one and send it through the serializer.

The below is essentially what I want to accomplish, but as soon as going 3 levels deep in SOQL it throws the error: 'SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object.'

I know I can run a seperate query to gather the Order Items Pricing in another query, but I am unsure how to append that to the 'jsonOrder' record so it is included in the json string.
Thanks in Advance!

Order jsonOrder = [SELECT Name, Id, Type, Partner_SAP_Customer_Number__c, PoNumber, PoDate, Quote_Number__c, Quote_ID__c, Opportunity_ID__c,Access_Type__c, CurrencyIsoCode, Opportunity_Pricing_Date__c, Order_Tier__c, Order_Reason__c, Requested_Shipping_Date__c, 
									Soldto_ID__c, Shipto_ID__c, Endcustomer_ID__c, Opportunity_Owner_ID__c,
									Shipping_Conditions__c, Payment_Terms__c, Inco_Terms__c, SAP_Order_Number__c,
									(SELECT Id, Product_Code__c, Revision_Material__c, VC_Material__c, Quantity,
									Validity_Period__c, Training_Location__c, Usage__c,
									ListPrice, Discount__c, WHT_Rate__c, Project_Credit_Discount_P__c,
									Fair_Value__c, Configuration__c, (SELECT Id, SAP_Pricing_Condition__c,  Pricing_Value__c, Pricing_Condition_Value__c
									FROM Order_Items_Pricing__r)	
									FROM OrderItems),
									(SELECT Id, Partner_Function_Code__c, SAP_ACCT_ID__c 	
									FROM Order_Partner_Functions__r)
									FROM Order
									WHERE id =: sforder.id LIMIT 1];
		OrderJsonString = JSON.serializePretty(jsonOrder); 
		System.debug('Json for SFOrder: '+OrderjsonString);
		return OrderJsonString;