Query records with their parent record name and their related records

Question

I'm trying to query records with their parent record name and their related records. This is my query:

SELECT Id, Name, Record_Type_Name__c, Engagement_Model__r.Name, (SELECT Contained_Product__r.Id, Contained_Product__r.ProductCode FROM Contained_Products__r) FROM Product2 WHERE Record_Type_Name__c = 'Subscription_Package'

My problem is with the Engagement_Model__r.Name, the query does not return it at all. Instead it returns Engagement_Model__c.

Anyone knows how to solve this?

Answer 1

Try using this as your query in Developer Console:

SELECT Id, Name, Record_Type_Name__c, (SELECT Name FROM Engagement_Model__r) , (SELECT Contained_Product__r.Id, Contained_Product__r.ProductCode FROM Contained_Products__r) FROM Product2 WHERE Record_Type_Name__c = 'Subscription_Package'

Please refer to this link for a similar issue/fix:

http://boards.developerforce.com/t5/General-Development/Problem-with-SOQL-relationship-queries/m-p/62895#M16379

Hope this helps.

Answer 2

I was testing this query in the Developer's Console which for some reason does not return the expected result. When testing the same query in the Workbench it works fine...

Answer 3

That should work. Obviously those are custom lookup fields on your Product2 so I can't try that query myself. Are you sure that there is a value in engagement_model__c lookup? What do you get for this:

List<Product2> prods = [SELECT Id, Name, Record_Type_Name__c, Engagement_Model__r.Name, (SELECT Contained_Product__r.Id, Contained_Product__r.ProductCode FROM Contained_Products__r) FROM Product2 WHERE Record_Type_Name__c = 'Subscription_Package'];
System.debug(prods[0].engagement_model__r.name);