0

I was trying something like along those lines but it's not working:

    SELECT
          Id
          (
              SELECT Id
              FROM Orders
              WHERE Custom_Field__c = 'SomeValue'
          )
   FROM Opportunity
   WHERE Somefield__c = :listOfSomeField
   AND 
       (StageName = 'Committed'
       OR
       Orders.size() > 0)

Is there a different way to do this within the query?

3 Answers 3

2

You would need to do a sub-query filter:

SELECT ...
FROM Opportunity
   WHERE Somefield__c = :listOfSomeField
   AND Id IN (SELECT OpportunityId FROM Order WHERE Custom_Field__c = 'SomeValue')

However, please note that you cannot combine this method with "OR", so you may actually need two different queries after all, or you'll have to do some post-processing in Apex Code to determine if the criteria was met.

1
  • Thank you, that's what I was wondering about. So I guess I won't be able to resolve it within the WHERE clause at this point. Thanks again!
    – Arthlete
    Commented May 15, 2018 at 17:18
3

If you just care about zero or non-zero, you can use Left Anti Join or Left Outer Join.

SELECT ... FROM Opportunity WHERE Id IN (SELECT OpportunityId FROM Order)
0

You can actually achieve this through aliasing a relationship field, although you have to use the sub-object as your primary object. Using your example, you could do the following to get the Ids of all opportunities that have more than 0 orders:

SELECT opp.Id 
FROM Order ord, Order.Opportunity__r opp
GROUP BY opp.Id
HAVING opp.Somefield__c IN :listOfSomeField
  AND COUNT(ord.Id) > 0

Just remember that the return datatype would be a list of AggregateResult.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .