10

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?

30

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' ? – hkguile Aug 27 '14 at 7:06
  • 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. – Danny Dev Nz Aug 27 '14 at 9:39
  • 1
    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? – Buttle Butkus Apr 30 '15 at 21:36
  • Hi @vladimir How to pass above code in soap apis – shivashankar m Jul 3 '18 at 10:38
6

ONE WAY:

$stockIds = Mage::getModel('cataloginventory/stock_item')
    ->getCollection()
    ->addQtyFilter('=', 30) //can be ->addQtyFilter('>=', 30), depending on requirement
    ->getAllIds();

$products = Mage::getModel('catalog/product')
    ->getCollection()
    ->addIdFilter($stockIds)
    ->setPageSize(10);

SECOND WAY:

$oCollection = Mage::getModel('catalog/product')
->getCollection()
->joinField(
    'qty',
    'cataloginventory/stock_item',
    'qty',
    'product_id=entity_id',
    '{{table}}.stock_id=1',
    'left'
)
->addAttributeToFilter('qty', array('eq' => 30));
3

The 'FIRST WAY' in @TBI Infotech's answer wont work, as the ->getAllIds() method returns the stock id, not the product ID. Instead you need to add this;

$stockIds = Mage::getModel('cataloginventory/stock_item')
->getCollection()
->addQtyFilter('>=', 30); 
//->getAllIds();

foreach($stockIds as $stock) {
   $idarray[] = $stock->getProductId();
}

$products = Mage::getModel('catalog/product')
->getCollection()
->addIdFilter($idarray)
->setPageSize(10);
  • This logic is the very clear, simple, with no magic. I wonder how efficient/slow/fast is $stock->getProductId()? – ahnbizcad Jan 26 '17 at 18:04
2

Most elegant way:

$products = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('*');
Mage::getSingleton('cataloginventory/stock')
    ->addInStockFilterToCollection($products);
1

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.

$product_collection->getSelect()->joinLeft(
        array(
            '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(),
        ''
    )->joinLeft(
        array(
            'cpsl' => $products->getTable('catalog/product_super_link')
        ),
        'cpsl.parent_id = e.entity_id',
        ''
    )->joinLeft(
        array(
            '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(),
        ''
    )->joinLeft(
        array(
            '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 . ")",
        ''
    )->group(
        'e.entity_id'
    )->columns(
        array(
            'qty' => new Zend_Db_Expr ("IF(`e`.`type_id` = 'simple', `css1`.qty, sum(css2.qty))")
        )
    )->having(
        'qty > 0'
);
  • I'm having a slow day today... Is it $products = Mage::getModel('core/resource'); and $product_collection = Mage::getModel('catalog/product') ->getCollection(); – elfling Jun 9 '17 at 14:18

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.