Whats the best way to get a collection with products which are in stock and have a positive qty.

I already use the following to filter in stock, but this give me also products with qty=0, because I allow backorders


How about his - you check that the quantity is greater than 0:

$in_stock_collection = Mage::getModel('catalog/product')
                         ->addAttributeToFilter('qty', array("gt" => 0));


Ok, so I suppose you need to grab the configurable products, and then for each item check if they are in stock? Unfortunately I do not have any stores with configurable items to test this on.

// get your configurable products collection
$_productCollection = Mage::getResourceModel('catalog/product_collection')

// then loop through and check each product if in stock
foreach ($_productCollection as $product) {

    $stock = Mage::getModel('cataloginventory/stock_item')->loadByProduct($product)->getQty();

    echo $product->getName() . ' has a stock of value of ' . $stock;
    echo '<br />';
  • Looks like, this works.. – Philipp Mar 25 '15 at 11:29
  • Ok, this doesn't work for configurable products.. since the stock of the configurable is zero – Philipp Mar 25 '15 at 12:52
  • I've edited my first answer, hopefully this will get you closer to what you want. I guess you'll still have to edit it a little – tecjam Mar 25 '15 at 13:48
  • Of course this would work, but this seems not very fast on big stores - I hoped that there's a better solution only in sql – Philipp Mar 25 '15 at 14:11

Late arriving answer, had a need to do some work on this code though, so here you go. It only takes a few joins, works for configurable and simple products, haven't tested against bundles.

$entity_type_id = Mage::getModel('eav/config')->getEntityType('catalog_product')->getEntityTypeId();
$attribute_id = Mage::getModel('eav/entity_attribute')->getIdByCode('catalog_product', 'status');

            'css1' => $products->getTable('cataloginventory/stock_status')
        'e.entity_id = css1.product_id and css1.stock_id =1 AND css1.website_id='.Mage::app()->getWebsite()->getId(),
            'cpsl' => $products->getTable('catalog/product_super_link')
        'cpsl.parent_id = e.entity_id',
            'css2' => $products->getTable('cataloginventory/stock_status')
        'css2.product_id = cpsl.product_id and css1.stock_id =1 AND css1.website_id='.Mage::app()->getWebsite()->getId(),
            'cpei' => 'catalog_product_entity_int'
        "cpei.entity_id = cpsl.product_id AND cpei.entity_type_id = $entity_type_id and cpei.attribute_id = $attribute_id AND (cpei.value IS NULL OR cpei.value = " . Mage_Catalog_Model_Product_Status::STATUS_ENABLED . ")",
            'qty' => new Zend_Db_Expr ("IF(`e`.`type_id` = 'simple', `css1`.qty, sum(css2.qty))")
        'qty > 0'
  • yeah, so did I, was surprised to find I'd written it! – Richard Nov 20 '15 at 9:54
  • what was $entity_type_id and $attribute_id referring to? – eddhall Jun 27 '18 at 11:56
  • I edited it in, but basically entity_type_id for product (usually 4) and the attribute_id for status – Richard Jun 27 '18 at 12:01
  • Ah thanks, I've replaced these in my code with Mage::getResourceModel('eav/entity_attribute')->getIdByCode('catalog_product', 'status') and Mage::getModel('eav/entity')->setType('catalog_product')->getTypeId(). I think I'm having issues with the 'column' section though as magento throws Column not found: 1054 Unknown column 'qty' in 'having clause', and running the assembled SQL query manually without adding the qty condition produces an SQL syntax error, any advice? – eddhall Jul 9 '18 at 15:02
  • check the generated SQL then, echo (string) $product_collection->getSelect(); before you try loading it – Richard Jul 9 '18 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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