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


2 Answers 2


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
    Commented Mar 25, 2015 at 11:29
  • Ok, this doesn't work for configurable products.. since the stock of the configurable is zero
    – Philipp
    Commented Mar 25, 2015 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
    Commented Mar 25, 2015 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
    Commented Mar 25, 2015 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
    Commented Nov 20, 2015 at 9:54
  • what was $entity_type_id and $attribute_id referring to?
    – eddhall
    Commented Jun 27, 2018 at 11:56
  • I edited it in, but basically entity_type_id for product (usually 4) and the attribute_id for status
    – Richard
    Commented Jun 27, 2018 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
    Commented Jul 9, 2018 at 15:02
  • check the generated SQL then, echo (string) $product_collection->getSelect(); before you try loading it
    – Richard
    Commented Jul 9, 2018 at 15:05

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.