Magento2 - product collection filter out of stock products

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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;