function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jennifer BERNARDJennifer BERNARD 

SOQL How to return all order items from orders if they answer criteria

Hello everyone !

I would like to remove all order items and their orders if ALL order items are 5 years old or older, and if their status is shipped. (both data can be found in Order Items object).

I wish to do that in one single SOQL request (entry request for a bulk job) to avoid any performance issue. Would it be possible to do so ?

For example
Order1
  |-> OrderItem1: shippingDate = 2015-01-01
  |-> OrderItem2: shippingDate = 2015-01-01
  |-> OrderItem3: shippingDate = 2019-01-01

Order2
  |-> OrderItem4: shippingDate = 2015-01-01
  |-> OrderItem5: shippingDate = 2015-01-01
  |-> OrderItem6: shippingDate = 2015-01-01

Order1 and related order items should not be deleted because at least one record is too recent.

Order2 and related orders should be deleted.

PriyaPriya (Salesforce Developers) 
 

The developer community recommends providing any attempts/code you've started, any errors you're getting, or where exactly you're struggling in achieving this while posting a question.


 
Jennifer BERNARDJennifer BERNARD
Hello Priya, thank you for your answer.

What I'm struggling to do is what was described in the previous post. For now, I've just made this request: 
SELECT Id, OrderId 
FROM OrderItem 
WHERE Shipping_Date__c < LAST_N_YEARS:5 
AND Order_Line_Status__c = 'Invoiced'
ORDER BY OrderId
The problem is, if you look at the example I provided above, OrderItem3 would no be retrieved by this request. So I have to make another request 
SELECT Count(Id) OrderItemsNumber, OrderId 
FROM OrderItem
WHERE OrderId IN :ordersIdsFromPreviousRequest
GROUP BY OrderId
Only then I can finally know if ALL order items, from orders, are older than 5 years.
As you can see I have made two requests to achieve that, and I was wondering if there's a way to do it in one single request.