0

A product is salable or not is determined based on below logic:

  • Product should be enabled.
  • Product should be marked in-stock.
  • qty -(minus) sum of reserved quantity in inventory_reservation table should be greater than 0. This is a important check. A product might have main quantity (qty), still it might not be salable.

Requirement: I have an array of configurable product skus and want to check if they are salable. To check a config is salable, assigned simple product should also be checked if they are salable.

<?php

namespace Algolia\CustomAlgolia\Observer;

use Magento\ConfigurableProduct\Api\LinkManagementInterface;
use Magento\Framework\Event\Observer;
use Magento\Framework\Event\ObserverInterface;
use Magento\Catalog\Api\ProductRepositoryInterface;

class ExcludeNotSalableProducts implements ObserverInterface
{
    /**
     * @var IsProductSalableInterface
     */
    private $isProductSalable;
    /**
     * @var LinkManagementInterface
     */
    private $linkManagement;
    /**
     * @var ProductRepositoryInterface
     */
    private $productRepository;

    /**
     * ExcludeNotSalableProducts constructor.
     * @param IsProductSalableInterface $isProductSalable
     * @param LinkManagementInterface $linkManagement
     * @param ProductRepositoryInterface $productRepository
     */
    public function __construct(
        LinkManagementInterface $linkManagement,
        ProductRepositoryInterface  $productRepository
    )
    {
        $this->linkManagement = $linkManagement;
        $this->productRepository = $productRepository;
    }

    /**
     * @inheritDoc
     */
    public function execute(Observer $observer)
    {
        $startTime = microtime(true);

        $configSkus = ['1001', '1002']; // sku of config products
        $parentSkusToExclude = [];
        foreach ($configSkus as $configSku) {
           echo $configSku  . PHP_EOL;
            $excludeParent = true;
            try {
                $childProducts =  $this->linkManagement->getChildren($configSku);
                foreach ($childProducts as $product) {
                    $isSalable = $this->productRepository->get($product->getSku())->isSalable();
                    if ($isSalable) {
                        $excludeParent = false;
                        break;
                    }
                }
            } catch (\Exception $exception) {
            }
            if ($excludeParent) {
                $parentSkusToExclude[] = $configSku;
            }
        }
        var_dump("done");
        var_dump($parentSkusToExclude);
        $endTime = microtime(true);
        echo 'took ' . ($endTime - $startTime) . ' seconds';
        exit;
    }
}

Above code takes around 50 second for 100 config products in my local system.

Question: What is the fastest way to determine if a config sku is salable?

2
  • Have you thought of populating the "Salable Quantity" view table for configurable products based on your logic? By default I can see that Configurable Products don't seem to be using "Salable Quantity" column.
    – M. Sonah
    Mar 5, 2020 at 21:18
  • I do not see a salable qty column for products. Can you elaborate about it?
    – amitshree
    Mar 7, 2020 at 19:22

1 Answer 1

2

One of the fastest way to figure out if config product is out of stock in my scenario is to do direct sql query and check the main qty and reserved quantity. I have achieved it using below code:

<?php

namespace Algolia\CustomAlgolia\Observer;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\Event\Observer;
use Magento\Framework\Event\ObserverInterface;

class ExcludeNotSalableProducts implements ObserverInterface
{
    /**
     * @var ResourceConnection
     */
    private $resourceConnection;

    /**
     * ExcludeNotSalableProducts constructor.
     * @param ResourceConnection $resourceConnection
     */
    public function __construct(
        ResourceConnection $resourceConnection
    )
    {
        $this->resourceConnection = $resourceConnection;
    }

    public function execute(Observer $observer)
    {
        $collection = $observer->getData('collection'); // config product data
        $connection = $this->resourceConnection->getConnection();
        $productRelation = $this->resourceConnection->getTableName('catalog_product_relation');
        $inventoryItemTable = $this->resourceConnection->getTableName('cataloginventory_stock_item');
        $catalogProductTable = $this->resourceConnection->getTableName('catalog_product_entity');
        $inventoryReservationTable = $this->resourceConnection->getTableName('inventory_reservation');

        $excludeConfigSku = [];
        foreach ($collection as $configProduct) {
            $excludeSku = true;
            $configSku = $configProduct['sku'];
            echo $configSku . PHP_EOL;
            $configId = $configProduct['entity_id'];
            $childProductsQuery = $connection->select()->from(
                $productRelation,
                ['child_id']
            )->where('parent_id = ?',$configId);
            $childProducts = $connection->fetchAll($childProductsQuery);
            foreach ($childProducts as $childProduct) {
                $childId = $childProduct['child_id'];
                $qtyQuery = $connection->select()->from(
                    $inventoryItemTable,
                    ['qty']
                )->where('product_id = ?', $childId);
                $qtyData = $connection->fetchRow($qtyQuery);
                $qty = (int) $qtyData['qty'];
                $skuDataQuery = $connection->select()->from(
                    $catalogProductTable,
                    ['sku']
                )->where('entity_id = ?',$childId);
                $skuData = $connection->fetchRow($skuDataQuery);
                $sku = $skuData['sku'];
                $inventoryQuery = $connection->select()->from(
                    $inventoryReservationTable,
                    ['sum(quantity) as reserved_qty']
                )->where('sku = ?', $sku);
                $entry = $connection->fetchRow($inventoryQuery);
                $reservedQty = ((int)$entry['reserved_qty']);
                if ($reservedQty !== 0) {
                    $qty = ($qty + $reservedQty);
                }
                if ($qty > 0) {
                    $excludeSku = false;
                    break;
                }
            }
            if ($excludeSku) {
                $excludeConfigSku[] = $configSku;
            }
        }
        $collection->addFieldToFilter('sku', ['nin' => $excludeConfigSku]);
    }
}

One other possibility I can think of which will be faster is, make simple product out stock (using sales order event) when sum of main quantity and reserved quantity is zero.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.