+ Start a Discussion
James BengelJames Bengel 

dot walking in nested SOQL query

I've been tinkering with a query as an exercise/proof of concept that would return an account, its associated contacts, opportunitires and cases, with some details of each related record.  And for the most part it works great. The part that breaks is when I try to pull in the contact name (first and alst) form the contact related to each Case -- as opposed to the contacts across the entire account.  If it comes ot it, I'll probably have ot manipulate the results in Apex anyway, so I can do the cleanup there, but I'm trying to work out if this is even possible, and so far coming up with only pat of an answer. (For the time being I'm just running these form the Qeury Editor in Developer Console.)

The part that works looks like this:
SELECT Id, Name,
 (SELECT FirstName, LastName, Title, Email, Phone FROM Contacts),
 (SELECT Name,StageName,Amount,CloseDate FROM Opportunities),
 (SELECT CaseNumber,ContactId,Subject,Priority,Status FROM Cases)
FROM Account
WHERE Name = 'Burlington Textiles Corp of America'

Adn this gives me everythign I ask for with no complaints, and I can also get any of the contact fields that are actually transcribed to the  Case record (Email, Phone, Fax, etc.).

What breaks, is when I try to get something form the Case Contact by reference like:

SELECT Id, Name,
 (SELECT FirstName, LastName, Title, Email, Phone FROM Contacts),
 (SELECT Name,StageName,Amount,CloseDate FROM Opportunities),
 (SELECT CaseNumber,ContactId,Contact.FirstName, Contact.LastName, Subject,Priority,Status FROM Cases)
FROM Account
WHERE Name = 'Burlington Textiles Corp of America'

Which basiclaly gets me everything I want, but it also gives me some extraneous information abotu how it GOT the first and last name of the contact.

"Contact":{"attributes":{"type":"Contact","url":"/services/data/v48.0/sobjects/Contact/0031U00001G0UCdQAN"},"FirstName":"Perry","LastName":"Noya"}

If I attempt to embed a second query for Contact within the subquery for Case, it just throws an error saying it doesn't understande the relationship, and am I missing the __r? (No.)

It may be that I just live with the additional text and clean it up in Apex, but I'm tryign to determine if I'm just missing something simple first.
Best Answer chosen by James Bengel
MagulanDuraipandianMagulanDuraipandian
SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object.
--
Magulan Duraipandian
www.infallibletechie.com

All Answers

MagulanDuraipandianMagulanDuraipandian
SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object.
--
Magulan Duraipandian
www.infallibletechie.com
This was selected as the best answer
James BengelJames Bengel
That's sort of what I figured the answer would be, but I didn't find anything to confirm it other than my own results.
I guess if there's good news, it's that you can actually get what you're after, you just have to parse the Contact (in this case) attributes for yourself. I coudl do it in Javascript easily enough, so the "what" is not so complex. But I'm only just beginning learnign Apex, so the "how" -- syntactically - will take a bit of work.