Adding Salable Quantity Filter in Product Collection in Magento 2

Hello Friends!!!

In this blog, we will learn how we can add the salable quantity filter to product collection.

Magento version >= 2.3.0, already provides Magento 2 Multi-Source Inventory (MSI) to manage the Magento 2 inventory and sources, which helps merchants manage the stocks of their stores’ catalog.

Why do we need this filter?

Suppose, you are using the default source for your store catalog, and then you enable MSI and create a few other sources.

For example, you created a new source ‘Test Source’ and assigned a ‘Test Stock’ to this source.
Then make this ‘Test Source’ as the default source of your website. And assigned inventory to products in Test Stock.

And after placing or completing the Magento 2 orders for some products which have inventory in both stocks(default and test), you switched back to ‘Default Source’.

Thus, in some scenarios, you can see the salable quantity might be changed from the in_stock quantity.
Refer to the following screenshots for example:

In the above screenshots, you can see there is a product with SKU ‘MG-7589654’ and it has in_stock quantity of 94, and the salable quantity is 67.

So after placing/completing the order for 67 quantity, its salable quantity will be 0(zero), and the in_stock quantity will be 27(look at the below screenshot).

So, at the front end, this product will be displayed and when we will try to add it to the cart, we will get a ‘The requested qty is not available‘ error message(refer to the below 3rd screenshot).

So, in this scenario, if we want to display only salable products at the front end. Then we can use this solution.

Solution:

Here, I have created a ListProducts.php Controller inside the <magento-root-dir.>/app/code/Webkul/Demo/Controller/Index/ directory.

<?php
/**
 * Webkul Software.
 *
 * @category  Webkul
 * @package   Webkul_Demo
 * @author    Webkul Software Private Limited
 * @copyright Copyright (c) Webkul Software Private Limited (https://webkul.com)
 * @license   https://store.webkul.com/license.html
 */

namespace Webkul\Demo\Controller\Index;

use Magento\Framework\App\Action\Action;
use Magento\Framework\App\Action\Context;
use Magento\Catalog\Model\ResourceModel\Product\CollectionFactory;

/**
 * ListProducts class to get list of products.
 */
class ListProducts extends Action
{
    /**
     * @var CollectionFactory
     */
    private $collectionFactory;

    /**
     * @var \Magento\CatalogInventory\Helper\Stock
     */
    private $stockFilter;

    /**
     * Initialize dependencies
     * 
     * @param Context $context
     * @param CollectionFactory $collectionFactory
     * @param \Magento\CatalogInventory\Helper\Stock $stockFilter
     * @return void
     */
    public function __construct(
        Context $context,
        CollectionFactory $collectionFactory,
        \Magento\CatalogInventory\Helper\Stock $stockFilter
    ) {
        $this->stockFilter = $stockFilter;
        $this->collectionFactory = $collectionFactory;
        parent::__construct($context);
    }

    /**
     * Execute method to show data
     */
    public function execute()
    {
        $collection = $this->collectionFactory->create()
            ->addFieldToSelect(["*", 'SUM(ir.quantity) as qtyTotal', 'name']);
        
        ///Add in stock filter
        $this->stockFilter->addInStockFilterToCollection($collection);

       ///Code to add is salable filter////
        $subquery = new \Zend_Db_Expr(
            '(SELECT SUM(`ir`.quantity) as rqty, `ir`.sku FROM `inventory_reservation` AS `ir` GROUP BY `ir`.sku)'
        );

        $collection->getSelect()->join(
            ['DT' => $subquery],
            "`e`.sku = `DT`.sku", []
        )->where("(`at_inventory_in_stock`.`qty` + `DT`.rqty) > 0");
        ////////////

        var_dump($collection->getData());
        die;
    }
}

When you will hit this controller on the browser, you will see the result in the below images.

Case 1: When product (SKU: MG-7589654) in_stock quantity is 94 and salable quantity is 67.