Before I start, I have read over a dozen stack exchange posts related to this, all of them suggesting pretty much what I'm using, which doesn't work for me hence the question.

I want to get all disabled products with quantity > 0 and for this I am using the following:

        $products = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('name', 'sku', 'qty', 'status')
        ->addAttributeToFilter('qty', array('gt' => 0))
        ->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_DISABLED));

    // debug
    $query = $products->getSelect()->__toString();

The query shows as this with no where clause:

SELECT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`name`, `at_qty`.`qty` FROM `catalog_product_flat_1` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1)

My results show everything in the store i.e with qty = 0 or any other qty and with any status.

I've tried every variation I can think of and find on stack exchange to no affect.

I would be grateful if anyone has any answer for this.

Many thanks

3 Answers 3


Marius led me to the answer in my situation which is a script I'm running separately from the site on the cron so I can add the following the beginning to set the store to admin:


or I could have emulated the admin store enter link description here


You don't see the real query with $query = $products->getSelect()->__toString(); because magento renders the query, when you load the collection. Therefore is the where not renderer yet.

to get the real query use $products->load(true);

  • Hi, that wrote the same query as above to the screen, it still has no where clause. Thanks.
    – kieron
    Feb 23, 2015 at 16:20

Try this:

$storeId = $this->storeId; //what ever this might be. I'm sure you have your logic, but it's not clear to me. I will take it for granted and say that it's a valid store id.

$store = Mage::getModel('core/store')->load($storeId);
$website = $store->getWebsite();

$products = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('name', 'sku', 'qty', 'status')
        ->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_DISABLED));

$select = $products->getSelect();
//join with the stock status table
$stockStatusModel = Mage::getModel('cataloginventory/stock_status');
$stockStatusModel->addStockStatusToSelect($select, $website);
$products->getSelect()->where('stock_status.qty > 0');

In order for this to work you need to call your script from an admin area or make sure that the flat products flag is disabled.
If the flat products flag is enabled, then the collection is using the flat tables that only index the enabled products. (so no disabled products to select).

  • Thanks, calling from the frontend, I now have a query checking qty > 0. The products flag is enabled and I can't really disable it given the size of the products collection involved, so I'll try and call it from Admin. I'm curious though why the addAttributeToFilter for qty I used above didn't work...
    – kieron
    Feb 23, 2015 at 16:34
  • Can I not set the store to zero to get admin results?
    – kieron
    Feb 23, 2015 at 16:36
  • it doesn't work if you just set the store id to 0. You need to add this at the top of the script: Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID); and your addAttributeToFilter by qty didn't work because qty is not a product attribute.
    – Marius
    Feb 23, 2015 at 20:06

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.