5

I could not find any relevant reference how to filter out products that are "Out of Stock" when dealing with product collection. Here is the code sample:

<?php

$collection = $this->productCollectionFactory->create();
$collection->setVisibility($this->catalogProductVisibility->getVisibleInCatalogIds());
$collection = $this->_addProductAttributesAndPrices($collection);

return $collection;

I have set "Display Out of Stock Products" to "No" but that has no affect on given code sample. Is there a common way (e.g. helper method) that can be used to filter out of stock products based on config value set?

1
  • did u find a solution to this? Feb 20, 2019 at 14:23

6 Answers 6

9

To bypass removing out of stock products in frontend (which could happens if you switched off showing out of stock items at stock options config page) we just need to set up a flag before we load the collection as

$collection->setFlag('has_stock_status_filter', true)

where $collection is instance of Magento\Catalog\Model\ResourceModel\Product\Collection

2
  • The only solution that works for me on 2.1.9. Thx a lot
    – cap340
    Mar 28, 2019 at 2:54
  • Cripes this was such a life saver when trying to remove old products we had disabled.
    – CarComp
    Aug 5, 2019 at 15:04
7

Use below code to your class to filter out of stock product

 /**
 * @var \Magento\CatalogInventory\Helper\Stock
 */
protected $_stockFilter;

on class constructor

 public function __construct(
    .....
     \Magento\CatalogInventory\Helper\Stock $stockFilter
    .....
) {
     $this->_stockFilter = $stockFilter;
}

in your method add the following code

public function getProducts(){
..........
 $this->_stockFilter->addInStockFilterToCollection($collection);
..........
}

here $collection is product collection.

6

Use method for this \Magento\CatalogInventory\Helper\Stock::addInStockFilterToCollection like below.

Example:

$collection = $this->productCollectionFactory->create();
$collection->setVisibility($this->catalogProductVisibility->getVisibleInCatalogIds());
$collection = $this->_addProductAttributesAndPrices($collection);
$this->stockFilter->addInStockFilterToCollection($collection);
4
  • This doesn't seem to work. Looking at M2 source github.com/magento/magento2/blob/2.0/app/code/Magento/… I don't see how it could even do that as $collection parameter is not passed by reference nor returned by method.
    – werd
    Jun 27, 2016 at 14:46
  • @werd Objects in PHP are passed by reference as default
    – Volvox
    Jan 10, 2017 at 14:35
  • @Volvox no they are not :)
    – werd
    Feb 1, 2017 at 14:44
  • @werd: Volvox is right. Objects are indeed passed by reference by default, at least within the sense you're using it here. (Technically the implementation is different than with primitive references, but the effect is the same. They're not copied/passed by value, which is what lets addInStockFilterToCollection work.) php.net/manual/en/language.oop5.references.php Jul 27, 2017 at 20:34
6

Use below code for filter out of stock product

protected $_linkFactory;

public function __construct(
    \Magento\Catalog\Model\Product\LinkFactory $linkFactory
) {
    $this->_linkFactory = $linkFactory;
}

    $collection = $this->_linkFactory->create()
        ->getProductCollection()->addAttributeToSelect('*')
        ->addAttributeToFilter(
            'status', array('eq' => \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED)
        )
    ;

    $collection->joinField(
        'qty', 'cataloginventory_stock_item', 'qty', 'product_id=entity_id', '{{table}}.stock_id=1', 'left'
    );
1
  • 2
    The Proper way to join cataloginventory_stock_status table. In you case you broken ability to not manage stock for product
    – KAndy
    Jun 27, 2016 at 13:01
3

There a couple of ways you can go about this. The first would be to use the stock helper Magento\CatalogInventory\Helper\Stock. This class contains the following two public methods:

addInStockFilterToCollection($collection)

&&

addIsInStockFilterToCollection($collection)

NOTE

is that both of these methods check to see if the config setting for showing out of stock products is enabled.

These methods use the addStockDataToCollection($collection, $isFilterInStock) from the Magento\CatalogInventory\Model\ResourceModel\Stock\Status class.

Here the $isFilterInStock variable will either join the stock index table via a left join. Additionally, if this variable is set to true, a stock status filter is added using a where statement. For example:

   /**
     * @param \Magento\Catalog\Model\ResourceModel\Product\Collection $collection
     * @param bool $isFilterInStock
     * @return \Magento\Catalog\Model\ResourceModel\Product\Collection $collection
     */
    public function addStockDataToCollection($collection, $isFilterInStock)
    {
        $websiteId = $this->getStockConfiguration()->getDefaultScopeId();
        $joinCondition = $this->getConnection()->quoteInto(
            'e.entity_id = stock_status_index.product_id' . ' AND stock_status_index.website_id = ?',
            $websiteId
        );

        $joinCondition .= $this->getConnection()->quoteInto(
            ' AND stock_status_index.stock_id = ?',
            Stock::DEFAULT_STOCK_ID
        );
        $method = $isFilterInStock ? 'join' : 'joinLeft';
        $collection->getSelect()->$method(
            ['stock_status_index' => $this->getMainTable()],
            $joinCondition,
            ['is_salable' => 'stock_status']
        );

        if ($isFilterInStock) {
            $collection->getSelect()->where(
                'stock_status_index.stock_status = ?',
                Stock\Status::STATUS_IN_STOCK
            );
        }
        return $collection;
    } 

So to answer your question, use thew addStockDataToCollection method in the Magento\CatalogInventory\Model\ResourceModel\Stock\Status class and set the $isFilterInStock variable to true.

-1

Use below code for filter out of stock Simple/Config products [magento 2 out of stock enabled configurable products]

$objectManager = $bootstrap->getObjectManager();

$state = $objectManager->get('Magento\Framework\App\State');

$state->setAreaCode('frontend');

$productCollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\CollectionFactory');

$collection = $productCollection->create();

$collection->setFlag('has_stock_status_filter', true);

$collection =
        $collection->addAttributeToSelect('*')
->addAttributeToSort('created_at', 'DESC')
->joinField('qty',
        'cataloginventory_stock_item',
        'qty',
        'product_id=entity_id',
        '{{table}}.stock_id=1',
        'left'
    )->joinTable('cataloginventory_stock_item', 'product_id=entity_id', ['stock_status' => 'is_in_stock'])
    ->addAttributeToSelect('stock_status')
    ->addFieldToFilter('stock_status', ['eq' => 0])
    ->addAttributeToFilter('status', \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED)
    ->addAttributeToFilter('type_id', ['eq' => 'configurable'])->load();

foreach ($collection as $product) {
    echo 'Config Product SKU: '.$product->getSku()."\n\n";
}

Note: Change $collection->addAttributeToFilter('type_id', ['eq' => 'configurable']) as per your requirements.

MySQL Query as follows:

SELECT e.*, at_qty.qty, cataloginventory_stock_item.is_in_stock AS stock_status, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS status FROM catalog_product_entity AS e LEFT JOIN cataloginventory_stock_item AS at_qty ON (at_qty.product_id=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN cataloginventory_stock_item ON (cataloginventory_stock_item.product_id=e.entity_id) INNER JOIN catalog_product_entity_int AS at_status_default ON (at_status_default.row_id = e.row_id) AND (at_status_default.attribute_id = '94') AND at_status_default.store_id = 0 LEFT JOIN catalog_product_entity_int AS at_status ON (at_status.row_id = e.row_id) AND (at_status.attribute_id = '94') AND (at_status.store_id = 14) WHERE ((cataloginventory_stock_item.is_in_stock = 0) AND (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1') AND (e.type_id = 'configurable')) AND (e.created_in <= '1589313000') AND (e.updated_in > '1589313000') ORDER BY e.created_at DESC;

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.