4

Imagine I have Custom Objects A, B and C.

A has a lookup field B__c that looksup a B field. C also has a lookup field B__c that lookups a B field. If we were to draw the lookup relations graphically, it would look like

A -> B <- C

Now I want to have a SOQL query that, given an A Id, fetches A, B and all the C elements of that B. The one->many name from B to C is Cs. How can I do this?

When I try something like

SELECT Id, (SELECT Id FROM A.B__r.Cs__c) FROM A__c

it gives the error

Error: Compile Error: First SObject of a nested query must be a child of its outer query

Improve this question

3 Answers 3

Reset to default
5

Re: comment

Is it possible to get A, B and all the C of B?

SELECT Id, Name,
    (SELECT Id, Name FROM A__r WHERE Id = :...),
    (SELECT Id, Name FROM C__r)
FROM B__c
WHERE Id = (SELECT B__c FROM A__c WHERE Id = :...)

Say Account, all it's Contacts and Opportunities where contact = John Doe

SELECT Id, Name,
    (SELECT Id, LastName FROM Contacts WHERE LastName = 'Doe'),
    (SELECT Id, StageName FROM Opportunities)
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName = 'Doe')

Experiment with WHERE Id IN and WHERE Id = depending on your needs etc but that's the baseline I'd say.

Improve this answer
5
  • In the first example, where do we get the C's of B?
    – Willem Mulder
    Jan 29, 2014 at 13:48
  • Updated. I've originally put the FROM B__c thing in wrong place.
    – eyescream
    Jan 29, 2014 at 13:56
  • That should work. Although I now have to iterate over the B values, and not over the A values. There's no way to make the 'root' query a query of type "FROM A__c", right?
    – Willem Mulder
    Jan 29, 2014 at 14:01
  • Not while having them all together. These relational queries mimic how it's laid out on the page: B plus 2 independent related lists, A and C... If you have some extra assumptions about the system (B-A being master-detail or maybe at most 1 A for each B) you could prepare upfront with making some rollup summaries (real or trigger-based) from A to B... Or relate them somehow across the rel. lists. My example completely ignores the existence of OpportunityContactRole object because it's less likely to be used...
    – eyescream
    Jan 29, 2014 at 14:12
  • Thanks! Then I've to work with B as root, or maybe do some extra queries.
    – Willem Mulder
    Jan 29, 2014 at 14:33
5

Depends on how you want your data returned. Two options are:

SELECT Id, B__c FROM C__c WHERE B__c IN (SELECT B__c FROM A__c)

or

SELECT Id, (SELECT Id FROM C__r) FROM B__c WHERE Id IN (SELECT B__c FROM A__c)
Improve this answer
2
  • The latter form, thanks. I didn't mention it in the question explicitly (although it can be seen in the query), and I'll accept this answer, but I actually also need A returned. So A, B and all the C of B. Is that possible?
    – Willem Mulder
    Jan 29, 2014 at 9:11
  • I updated the question to reflect this 'requirement' better. Is it possible to get A, B and all the C of B?
    – Willem Mulder
    Jan 29, 2014 at 11:21
0

I am developing an iOS app in Swift and needed to get the first and last name from the Lookup(Contact) field in a SOQL query. That Lookup(Contact) field was given the API Name FirstField__c. In Xcode, as much as I tried, I could not use the __r method. Instead I found it easier to create a second custom read only Formula (Text) field with the formula:

FirstField__r.FirstName & " "& FirstField__r.LastName

That new field was given the API Name SecondField__c and then in my SOQL, I easily queried that new field.

I hope this helps someone in a similar situation.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .