You need to sign in to do that
Don't have an account?
James 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.
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.
--
Magulan Duraipandian
www.infallibletechie.com
All Answers
--
Magulan Duraipandian
www.infallibletechie.com
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.