2

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.

  • 1
    Does OpportunityLineItem have a relationship field that points to your MyObject__c object? I answered a similar question here about 2.5 years ago that you might find helpful. – Derek F Sep 20 at 14:31
  • OpportunityLineItem does not have a custom field that maps to MyObject__c. The id field in that table maps to OpportunityLineItemID__c in myobject__c though. Perhaps I need to run the from myobject__c rather than OpportunityLineItem and then use OpportunityLineItem__r... for the fields I want? – user3783243 Sep 20 at 14:38
3

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)];
  • Thanks, this makes sense. – user3783243 Sep 20 at 15:26
1

Perhaps something like this would work:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.