I have this solution to filter out of stock products by joining a field from cataloginventory/stock_item (the is_in_stock field) from my product collection and this works as intended with non-flat-products. But with flat products enabled it breaks, it shows products which have inventory but have the status of "out of stock". Presumably because the fields aren't available when using flat products, but I'm not sure which other fields I should use then. Here is my code:

if (!Mage::getStoreConfig('cataloginventory/options/show_out_of_stock')) {
        $collection->joinField('is_in_stock', 'cataloginventory/stock_item', 'is_in_stock', 'product_id=entity_id', '{{table}}.stock_id=1', 'left');
        $collection->addAttributeToFilter('is_in_stock', array('eq' => 1));

I tried looking at core code and saw that they use cataloginventory/stock_status for their join, and then filter on the field stock_status. I tried to do this, but this doesn't work. How can I filter out of stock products when using flat products?

  • Did you run indexes 'product flat data' and 'catalog inventory' after you enabled flat products? – Vicky Mar 24 '14 at 15:18
  • @Vicky Yes I did – Tim Wachter Mar 24 '14 at 15:29
  • Are we talking about simple products? Did you make sure the stock qty > minimum stock qty threshold? – Julien Lachal Mar 27 '14 at 11:03
  • Not just simple products, also configurable and all that. Note, the code I posted works for non-flat-product installations, on installations with flat-products enabled this doesn't work – Tim Wachter Mar 27 '14 at 14:53

When filtering by stock status on a product collection you can use the standard way in magento as follows.


This will eventually call setInStockFilterToCollection on Mage_CatalogInventory_Model_Resource_Stock

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

        '(' . join(') OR (', $cond) . ')'
    return $this;
| improve this answer | |
  • 1
    It's so easy if you know what you are doing... Thanks! – Tim Wachter Mar 31 '14 at 7:30
  • Forgot your bounty, here it is! – Tim Wachter Apr 1 '14 at 7:11
  • There is one problem with this, when a configurable product has child products which are all out of stock and the 'main product' is still in stock (Default Magento behavior) it still returns that main product. (while all the configurations/child products are out of stock) Is there a way around this? Or should I make my own version of the function from above? – Tim Wachter Apr 2 '14 at 9:16

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.