0

I have this query but it is returning orders where there a no orderitems present, how can i only select orders that have order items in them?

SELECT EffectiveDate, Description, id, (select order.id from orderitems) FROM Order

2 Answers 2

0

Try something like that:

List<Order> orders = [
   SELECT Id, EffectiveDate, Description, (SELECT OrderId FROM OrderItems) 
   FROM Order 
   WHERE Id IN (SELECT OrderId FROM OrderItem)
];
4
  • 2
    The explanation is as important as the solution itself.
    – Derek F
    Commented Oct 2, 2022 at 15:05
  • Thanks @DerekF, I will apply it to my next answers. :) Commented Oct 2, 2022 at 15:10
  • @DerekF Can you please help ? salesforce.stackexchange.com/questions/386763/… Commented Oct 3, 2022 at 15:47
  • @KishanPatel Directly asking members for help like that is considered rude.
    – Derek F
    Commented Oct 3, 2022 at 16:00
3

A query can do this (only return records with >= 1 child record) through a few different methods:

  • Use a semi join
  • Have a rollup summary field (on Order) that counts the number of order items, and use that as a filter in the WHERE clause
  • Change your query to be based on OrderItem instead

Of course, you could always just process the results in Apex too (which may or may not end up being the easiest approach).

A semi-join is a subquery in the WHERE clause, and it's generally used to find Ids on related records. It'd look something like this

SELECT Id FROM Order WHERE Id IN (SELECT OrderId FROM OrderItem WHERE OrderId IN :orderIds)

Unrestricted queries are a red flag, and set you up for disaster sooner rather than later. The semi-join itself can only select a single field, and it must be an Id or relationship field.

It may seem a bit circular, but such a query does make sense.

  • OrderId IN :orderIds helps restrict the number of rows you're querying. It may not count towards the queried rows governor limit, but fewer rows still means better performance
  • Selecting the OrderId in the semi-join, based on OrderItem will only return Order Ids that have Order Items
  • WHERE Id IN (SELECT...) is what actually filters out the Orders without items

You must log in to answer this question.

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