Best way to keep product attribute updated which depends on order status

Question

I've a product attribute "Pending Shipping quantity" available for all products. It value will be counted based on Order status. If an order is placed for product having SKU 100 and order state is processing, Pending shipping quantity of this product will be the quantity ordered.

So, for SKU 100, if there is an order in processing state, the quantity ordered in that order will be the Pending shipping quantity.( If same product is available in another order in processing state, Pending shipping quantity will be the sum of the quantity of product ordered in both the orders).

Pending shipping quantity should decrease if the order status changes.

What should be the best approach to achieve it?

Possibility 1:

1. Use observer method sales_order_place_after or sales_order_save_after and increase Pending shipping quantity when state changes to processing.
2. Use observer method sales_order_save_after and decrease Pending shipping quantity if state change from processing to something else.

Possibility 2:

Get sales order collection filtered by state processing. Get all items attached to these orders and based on ordered quantity Pending shipping quantity.Though this process looks a bit more complex.

Is there any other way to achieve it keeping performance in mind?

Answer 1

For real-time processing with minimum performance impact, I would put an observer on the sales_order_save_after event and use Mage::getResourceModel('catalog/product_action')->updateAttributes() method to update the attribute on the products. This method bypasses Magento's flats and cache updates, assuming your attribute is not indexable and not needed on the frontend.

If performance requirements are really tight, then I would probably push order save events to a queue and process the queue asynchronously, or set up a cron job that processes orders updated in the last X minutes interval.

Cheers