Using ORM get all disabled products with qty > 0

Question

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')
        ->addStoreFilter($this->storeId)
        ->joinField('qty',
            'cataloginventory/stock_item',
            'qty',
            'product_id=entity_id',
            '{{table}}.stock_id=1',
            'left')
        ->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

Answer 1

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:

Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

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

Answer 2

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);

Answer 3

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')
        ->addStoreFilter($storeId)
        ->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).