How to get products by stock qty?

  • I have the following code:

    umask(0);
    Mage::app();
    $category =new Mage_Catalog_Model_Category();
    $category->load($cid);
    
    if ($status == "2") {
        $products = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('*') 
        ->addFieldToFilter('status',Mage_Catalog_Model_Product_Status::STATUS_DISABLED);
        $products->load();
    }
    
    if ($status == "1") {
        $products = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('*');
        $products->load();  
    }
    

    I want to add a filter of stock qty to filter the products, I tried:

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

    But not succuessful, any ideas?

  • You need to create a join with the cataloginventory/stock_item table to get the stock qty for a product, you could use:

    $collection = Mage::getModel('catalog/product')
         ->getCollection()
         ->addAttributeToSelect('*')
         ->joinField('qty',
             'cataloginventory/stock_item',
             'qty',
             'product_id=entity_id',
             '{{table}}.stock_id=1',
             'left'
         )->addAttributeToFilter('qty', array('gt' => 0));
    

    what the meaning of this row '{{table}}.stock_id=1' ?

    In short it means you are loading the qty from the default stock item. {{table}} in this context translates to the cataloginventory_stock_item table and stock_id = 1 indicates that this is the default stock item.

    I'm doing `$coll = Mage::getResourceModel('catalog/product_collection')` and using your join field and `addAttributeToFilter`. The collection is loading just fine. But the filter is not working: `->addAttributeToFilter('qty', array("gt" => 0))`. Looking at `$coll->getSelect()`, there is no reference to `qty` in the `WHERE` clause. Any idea why?

    Hi @vladimir How to pass above code in soap apis

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM

Tags used