function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
GaryP.ax988GaryP.ax988 

Query nested objects over WS API..

I have seen a few discussions on the varying boards and I still can't determine if this is possible or not..    

 

Given this relationship

 

Case -> Account -> Contacts -> CustomObject..  

 

Can I retrieve these detials with a single query over the web service API?   

 

The docs imply 5 levels on the parent child nesting.. but anything beyond two causes errors.. 

 

for instance this:

Select CaseNumber, (Select Contacts.FirstName from Case.Account), Contact.FirstName, Contact.LastName from Case

 

causes: Didn't understand relationship 'Case.Account' in FROM part of query call

 

if I try:

Select CaseNumber, (Select FirstName from Account.Contacts), Contact.FirstName, Contact.LastName from Case 

 

it causes:   First SObject of a nested query must be a child of its outer query

 

Any siggestions?  I would really hate to have to ask for the account and then ask for the contacts and then ask for the custom objects.. that's a very chatty web service (maybe that's why they keep pushing this "Chatter" thing.. :-) )

 

Thanks in advance

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

You can go up five levels via parent relationship names, but only down one level via child relationship subqueries.

 

If CustomObject is a child of contacts, then you will need at minimum two queries; you won't be able to grab the case details or the custom object details in the same pass.

 

If CustomObject is a lookup from contacts, then you need only one query:

 

 

SELECT account fields,(SELECT case fields FROM Cases WHERE ...),(SELECT contact fields, custom_object__r.fields FROM Contacts) FROM Account WHERE ...

Alternatively, if you're looking to save bandwidth, just write an Apex Code Web Service class, have it perform the multiple queries, aggregate the results into a single return object, and have your integration consume that instead.

 

 

I know it's frustrating to have to go about it this way, especially since most implementations of SQL will let you do this in an efficient manner, but it's something we have to deal with.

All Answers

Ritesh AswaneyRitesh Aswaney
The subquery can only apply to a child of the main object which the select is on
Eg
Select Id, Name, (Select Id, name from Contacts) from Account

However you can still do
Select Id, name, contact.account.name from contact
GaryP.ax988GaryP.ax988

 

Ritesh Aswaney wrote:
The subquery can only apply to a child of the main object which the select is on
Eg
Select Id, Name, (Select Id, name from Contacts) from Account

However you can still do
Select Id, name, contact.account.name from contact

 

OK so basically the queries only allow for 1 level of nesting..    that's a real shame.. it does make things chatty since I have a case number so to get the case, account and contacts and related contact objects I need 2 web service queries..     I'll have to make it work like this..     But I must admit I'm kind of surprised.. that forces integraters into making a lot of extra calls to Salesforce..  I would think they'd want to minimize that.. 

 

Thanks for the answer!

Gary
sfdcfoxsfdcfox

You can go up five levels via parent relationship names, but only down one level via child relationship subqueries.

 

If CustomObject is a child of contacts, then you will need at minimum two queries; you won't be able to grab the case details or the custom object details in the same pass.

 

If CustomObject is a lookup from contacts, then you need only one query:

 

 

SELECT account fields,(SELECT case fields FROM Cases WHERE ...),(SELECT contact fields, custom_object__r.fields FROM Contacts) FROM Account WHERE ...

Alternatively, if you're looking to save bandwidth, just write an Apex Code Web Service class, have it perform the multiple queries, aggregate the results into a single return object, and have your integration consume that instead.

 

 

I know it's frustrating to have to go about it this way, especially since most implementations of SQL will let you do this in an efficient manner, but it's something we have to deal with.

This was selected as the best answer
GaryP.ax988GaryP.ax988

 

sfdcfox wrote:

You can go up five levels via parent relationship names, but only down one level via child relationship subqueries.

 

If CustomObject is a child of contacts, then you will need at minimum two queries; you won't be able to grab the case details or the custom object details in the same pass.

 

If CustomObject is a lookup from contacts, then you need only one query:

 

 

SELECT account fields,(SELECT case fields FROM Cases WHERE ...),(SELECT contact fields, custom_object__r.fields FROM Contacts) FROM Account WHERE ...

Alternatively, if you're looking to save bandwidth, just write an Apex Code Web Service class, have it perform the multiple queries, aggregate the results into a single return object, and have your integration consume that instead.

 

 

I know it's frustrating to have to go about it this way, especially since most implementations of SQL will let you do this in an efficient manner, but it's something we have to deal with.

 

Thanks!    I did the 2 query method and had planned to write a custom WS object to use in the WS..  I was avoiding the custom object to "save time" as its just a demo/poc but now that I'm actually in there I need to do the custom WS object anyways..    

 

Thanks again for the feedback.. it confirms what I had thought..