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
Try something like that:
List<Order> orders = [
SELECT Id, EffectiveDate, Description, (SELECT OrderId FROM OrderItems)
FROM Order
WHERE Id IN (SELECT OrderId FROM OrderItem)
];
A query can do this (only return records with >= 1 child record) through a few different methods:
Order
) that counts the number of order items, and use that as a filter in the WHERE
clauseOrderItem
insteadOf 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 performanceOrderId
in the semi-join, based on OrderItem
will only return Order Ids that have Order ItemsWHERE Id IN (SELECT...)
is what actually filters out the Orders without items