I'm trying to join two standard objects, and a custom object. I need to join OpportunityLineItem
, Opportunity
, and MyObject
. All tables have references to each other.
MyObject
has OpportunityLineItemID__c
which points back to OpportunityLineItem
's Id
. The OpportunityLineItem
has OpportunityId
which maps back to Opportunity
's Id
.
In standard SQL I'd write this as:
SELECT *
FROM OpportunityLineItem as oli
join Opportunity as o
on oli.OpportunityId = o.id
join myobject as m
on m.id = oli.id
where ...
I tried with the __r
notation but I get an error about the relations:
Didn't understand relationship 'myobject__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
My SQOL attempt:
SELECT myobject__r.fieldIwantInThisTable
FROM OpportunityLineItem where ....
I'm leaving out the Opportunity
for MVCE.
Best Answer
Salesforce, for one reason or another, doesn't allow us to make relationship fields that target OLI (OpportunityLineItem).
We can hold an OLI Id in a
text
field, but that won't allow you to traverse the relationship in SOQL.Your options here are to break this into two queries (one to pull your
Custom_Object__c
records based on the Ids of the OLIs you are working with, one to pull the OLIs and related data from their Opportunities), or maintain a relationship field onMyObject__c
toOpportunity
so you can use two parent-child subqueries (left outer joins).The two queries method might look like this:
The one query approach might look like this: