Mdthbs

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.

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 on MyObject__c to Opportunity so you can use two parent-child subqueries (left outer joins).

The two queries method might look like this:

// You'll want this to be a map so that you can easily grab the appropriate OLI when you're

//   working with MyObject__c records.

// To get Opportunity data in a query on OLI, you use Opportunity.<field api name> for each field

// The relationship between OLI and Opportunity is a standard field, OpportunityId.

// The child relationship name for standard relationship fields is usually just <object name>, e.g. Opportunity 

//   (we drop the "Id" bit from the field name)

Map<Id, OpportunityLineItem> olisMap = new Map<Id, OpportunityLineItem>([SELECT Id, UnitPrice, Opportunity.Amount, Opportunity.AccountId FROM OpportunityLineItem WHERE Id IN :oliIds]);



List<MyObject__c> myObjs = [SELECT Id, OtherField__c FROM MyObject__c WHERE OpportunityLineItem_Id__c IN :olisMap.keySet()];

The one query approach might look like this:

// This approach also uses what Salesforce calls a semi-join

// Basically, a subquery in the WHERE clause that tells us what to query in the main query)

// I assume MyObjects__r is the child relationship name between MyObject__c and Opportunity,

//   you'll likely need to change this.

// Newlines here are just for readability

List<Opportunity> oppsList = [

    SELECT

        Id, Amount, 

        (SELECT Id, UnitPrice FROM OpportunityLineItems__c), 

        (SELECT Id FROM MyObjects__r) 

    FROM Opportunity 

    WHERE Id IN (SELECT OpportunityId FROM OpportunityLineItem WHERE Id IN :oliIds)];

Perhaps something like this would work:

SELECT Id, OpportunityId, <Other fields> FROM OpportunityLineItem WHERE OpportunityId IN: (SELECT <Opportunity Id Lookup Field> FROM myobject__c)