Does anybody have a query handy, or point me in the best direction for what tables to query, directly, to get a summary of.

{magento product ID, my SKU, qty of that product} on orders with status {Pending, Hold}. I do not care about what orders they are associated with, customers, etc.

I have a whole set of programs that look at our ERP system, point of sale system, inventory reserve system and look at product availability and via API turns products with low qty off in Magento - works great, but these are not "magento aware" of new orders on the web and we do not process our web sales in real time, but I would like to add my open orders on my website into my availability counts and algorithms.

1 Answer 1


Try below code. This will work fine.

$orders = Mage::getModel("sales/order")->getCollection()
        ->addFieldToFilter('status', array('pending', 'processing', 'hold'))
        ->setOrder('created_at', 'desc');

$orderIds = $orders->getAllIds();
$total_count = count($orderIds);

foreach ($orderIds as $orderId) {
    $order = Mage::getModel('sales/order')->load($orderId);
    $customerId = $order->getCustomerId();
    $items = $order->getItemsCollection();
    foreach ($items as $i => $item) {
        $sku = $item->getSku();
        $product_id = $item->getProductId();
        $qty = $item->getQtyOrdered();
        $price = $item->getPrice();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.