How to get product collection of out of stock items - opposite of addInStockFilterToCollection()?

let's say that $collection is your product collection that you build like this:

$collection = Mage::getModel('catalog/product')->getCollection()
    ->...additional filters here...;

now do this to your collection. This joins the collection with the stock status table.

$website = Mage::app()->getWebsite();
Mage::getModel('cataloginventory/stock_status')->addStockStatusToSelect($collection, $website);

Now you can filter the out of stock products:

$collection->getSelect()->where('stock_status.stock_status = ?', 0);

Your example does not take the value for "use config" into account.

Let's have a look at how addInStockFilterToCollection works:

public function addInStockFilterToCollection($collection)
{
    $this->getResource()->setInStockFilterToCollection($collection);
    return $this;
}

OK, it's delegating to another method:

public function setInStockFilterToCollection($collection)
{
    $manageStock = Mage::getStoreConfig(Mage_CatalogInventory_Model_Stock_Item::XML_PATH_MANAGE_STOCK);
    $cond = array(
        '{{table}}.use_config_manage_stock = 0 AND {{table}}.manage_stock=1 AND {{table}}.is_in_stock=1',
        '{{table}}.use_config_manage_stock = 0 AND {{table}}.manage_stock=0',
    );

    if ($manageStock) {
        $cond[] = '{{table}}.use_config_manage_stock = 1 AND {{table}}.is_in_stock=1';
    } else {
        $cond[] = '{{table}}.use_config_manage_stock = 1';
    }

    $collection->joinField(
        'inventory_in_stock',
        'cataloginventory/stock_item',
        'is_in_stock',
        'product_id=entity_id',
        '(' . join(') OR (', $cond) . ')'
    );
    return $this;
}

This joins the inventory table with the following conditions:

  1. Product does not use global configuration AND has "manage stock" set to "yes" AND is in stock

    OR

  2. Product does not use global configuration AND has "manage stock" set to "no"

    OR

  3. Product uses global configuration AND if the global configuration is "manage stock = yes", is in stock

You need to invert the conditions as follows:

  1. Product does not use global configuration AND has "manage stock" set to "yes" AND is not in stock

    OR

  2. Product uses global configuration AND global configuration is "manage stock = yes" AND is not in stock

Explanation: You take only the conditions where in_stock is actually checked and change the comparison to 0. The conditions where in_stock is not checked ("manage stock" = "no") mean that the product is always in stock, regardless of the stock status, so we don't include them in our "out of stock" query.

Then this is your code:

public function setOutOfStockFilterToCollection($collection)
{
    $manageStock = Mage::getStoreConfig(Mage_CatalogInventory_Model_Stock_Item::XML_PATH_MANAGE_STOCK);
    $cond = array(
        '{{table}}.use_config_manage_stock = 0 AND {{table}}.manage_stock=1 AND {{table}}.is_in_stock=0'
    );

    if ($manageStock) {
        $cond[] = '{{table}}.use_config_manage_stock = 1 AND {{table}}.is_in_stock=0';
    }

    $collection->joinField(
        'inventory_in_stock',
        'cataloginventory/stock_item',
        'is_in_stock',
        'product_id=entity_id',
        '(' . join(') OR (', $cond) . ')'
    );
    return $this;
}