9

I have a requirement to display a category's products in two lists - one for in stock items, the other for out of stock items.

I'm using

Mage::getSingleton('cataloginventory/stock')->addInStockFilterToCollection()

to filter my product collection for the in stock items, but there doesn't appear to be an equivalent method to filter for out of stock items - I've looked at the Mage_CatalogInventory_Model_Stock model, which is where the aforementioned method is defined.

I have seen the following example for retrieving out of stock products:

$collection->joinField(
                    'is_in_stock',
                    'cataloginventory/stock_item',
                    'is_in_stock',
                    'product_id=entity_id',
                    '{{table}}.stock_id=1',
                    'left'
            )
            ->addAttributeToFilter('is_in_stock', array('eq' => 0));

...but surely this isn't only or best way to achieve this?

3

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);
  • Would this work no matter what configuration options are set for each product's inventory? (E.g. is a product that's not stock managed in stock or out of stock?) – BrynJ Nov 4 '15 at 14:14
  • It should work, because the stock table is indexed and it takes into account all the possible settings. You just have to make sure your stock index is up to date – Marius Nov 4 '15 at 14:38
  • Thanks for confirming. This seems like a nice "lightweight" approach to getting this data. – BrynJ Nov 4 '15 at 15:30
  • Using the stock status index table instead of the stock item table is indeed the best solution. – Fabian Schmengler Nov 5 '15 at 7:58
  • I had to send the product select as the first argument to the function Mage::getModel('cataloginventory/stock_status')->addStockStatusToSelect($collection->getSelect(), $website); – minlare Mar 7 '17 at 10:15
2

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;
}
  • Great detailed answer. So you'd suggest creating a module for this, to extend the Mage_CatalogInventory_Model_Stock model? – BrynJ Nov 4 '15 at 15:29
  • 1
    No rewrite or extending. You can put this method anywhere as it does not use $this. It could as well be plain function. I would create a separate module and make it a helper method (or if you need this for another module, add it to the helper of that module) – Fabian Schmengler Nov 4 '15 at 15:38
1

The following code snippet will return you the products of a category that have status 'Enable' , Visibility 'catalog,search' and Stock Availability ' Out of stock'.

$productDetails = Mage::getModel('catalog/category')->load($cat_id)
                  ->getProductCollection()
                  ->addAttributeToSelect('*');

$productDetails->addAttributeToFilter('visibility', 4); 
$productDetails->addAttributeToFilter('status', 1); 
$productDetails->joinField('is_in_stock',
                            'cataloginventory/stock_item',
                            'is_in_stock',
                            'product_id=entity_id',
                            'is_in_stock=0',
                            '{{table}}.stock_id=1',
                            'left');
0

You can try this.

 $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',
            '{{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=0';
        } 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) . ')'
        );

Or you can try this

  $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',
            '{{table}}.use_config_manage_stock = 0 AND {{table}}.manage_stock=0',
        );

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


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

Not sure 100%.

  • Thanks @AmitBera. Can you please provide some context for your answers, and explain the differences? – BrynJ Nov 3 '15 at 14:25

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.