[SalesForce] SOQL: Select Opportunity based on Product fields

I am trying to query Opportunities which have a Product matching certain criteria on, something like below:

SELECT Name 
FROM Opportunity 
WHERE type != 'renewal' 
  AND Id IN (SELECT OpportunityId 
             FROM OpportunityLineItem 
             WHERE pricebookentry.product2.custom__c = 'value')

While the above doesn't cause an error no rows are returned. Is this possible or is my soql not formed correctly?

Edit:
Splitting the SOQLS, the Opportunity part returns rows as expected. The OpportunityLineItem part doesn't:

SELECT OpportunityId
FROM OpportunityLineItem
WHERE pricebookentry.product2.custom__c = 'value'

So I believe I am doing something wrong with the OLI to Product part: pricebookentry.product2.custom__c

Best Answer

It appears the issue was elsewhere in the class and not with the SOQL in my post. It is working as intended now.

Related Solutions

[SalesForce] SOQL to update a field of an OpportunityLineItem in the opportunity in use

You need to query by a particular id, or a list of ids, instead of all available ids. You can use a bind to provide an apex variable to a query for use in a filter.

From the Using Apex Variables in SOQL:

Account A = new Account(Name='xxx');
insert A;
Account B;

// A simple bind
B = [SELECT Id FROM Account WHERE Id = :A.Id];

You can also use a Id or a List<Id>.

List<Id> ids = new List<Id>(); // set from somewhere, or get from a list of sObjects
Id someId = 'a0Cc000000PAWBv'; // For example only, avoid hardcoding ids

List<Account> accounts = [SELECT ID, Name FROM Account WHERE Status = 'Active' AND Id IN :Ids];

Account account = [SELECT ID, Name FROM Account WHERE Status = 'Active' AND Id = :someId LIMIT 1];

This is a general example on how to use these variables in queries. Try to modify your code to use these, and either edit your question, or ask a new question if you encounter issues with your code.

[SalesForce] How to do a SOQL left join

Try this:

SELECT ID, (SELECT External_AdServer_ID__c FROM opportunityLineItems),(SELECT ID,Drop_ID__c FROM Opportunity_Line_Item_Details__r)From Opportunity

you can use this also:

SELECT External_AdServer_ID__c, (SELECT Id,Drop_ID__c FROM Opportunity_Line_Item_Details__r) FROM opportunityLineItem

Best Answer

Related Solutions

[SalesForce] SOQL to update a field of an OpportunityLineItem in the opportunity in use

[SalesForce] How to do a SOQL left join

Related Topic