SOQL Join Opportunity, OpportunityLineItem, and Custom Object

Question

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.

Answer 1

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)];

Answer 2

Perhaps something like this would work:

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