Magento2 - Filter out Configurable Products with out of stock child products from product collection

Question

I want to filter the custom product collection by stock. My code works only with simple products. Here is my code.

<?php 
namespace Company\Module\Block;
use Magento\Catalog\Api\CategoryRepositoryInterface;


class customProducts extends \Magento\Catalog\Block\Product\ListProduct
{

    protected $productFactory; 
    protected $product;
    protected $_categoryCollectionFactory;
    protected $_categoryHelper;
    protected $categoryFlatConfig;

    public function __construct(
        \Magento\Catalog\Block\Product\Context $context, 
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory  $productFactory,
        \Magento\Catalog\Model\ResourceModel\Category\CollectionFactory $categoryCollectionFactory,
        \Magento\Catalog\Model\Indexer\Category\Flat\State $categoryFlatState,
        \Magento\Catalog\Helper\Category $categoryHelper,
        \Magento\Catalog\Model\Product $product,
        \Magento\Framework\Data\Helper\PostHelper $postDataHelper,
        \Magento\Catalog\Model\Layer\Resolver $layerResolver,
        CategoryRepositoryInterface $categoryRepository,
        \Magento\Framework\Url\Helper\Data $urlHelper,
        array $data = [])
    {
        $this->product = $product;
        $this->_categoryCollectionFactory = $categoryCollectionFactory;
        $this->_categoryHelper = $categoryHelper;
        $this->productFactory = $productFactory;
        $this->categoryFlatConfig = $categoryFlatState;
        parent::__construct($context, $postDataHelper, $layerResolver,$categoryRepository, $urlHelper, $data);
    }    


    /**
     * Retrieve loaded category collection
     *
     * @return AbstractCollection
     */
    protected function _getProductCollection()
    {
        $collection = $this->productFactory->create();  
        $collection->addAttributeToFilter('visibility', \Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH);
        $collection->addAttributeToFilter('status',\Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED);
        $collection->addAttributeToSelect('*');
        $joinTable = $collection->getTable('customtable_product');
        $collection->getSelect()
        ->join($joinTable.' as product','e.entity_id = product.product_id and product.product_status = 1',array('*'));
        $collection->joinField(
        'qty', 'cataloginventory_stock_item', 'qty', 'product_id=entity_id', '{{table}}.stock_id=1 and {{table}}.is_in_stock=1', 'inner' );     

        $this->_productCollection = $collection;
        return $this->_productCollection;



    }


}

Please help. Thanks

Answer 1

You can use this in order to filter a product collection with stock :

\Magento\CatalogInventory\Helper\Stock $stockHelper

$this->stockHelper->addInStockFilterToCollection($collection);

The core function used is :

/**
 * Adds filtering for collection to return only in stock products
 *
 * @param \Magento\Catalog\Model\ResourceModel\Product\Link\Product\Collection $collection
 * @return void
 */
public function addInStockFilterToCollection($collection)
{
    $manageStock = $this->scopeConfig->getValue(
        \Magento\CatalogInventory\Model\Configuration::XML_PATH_MANAGE_STOCK,
        \Magento\Store\Model\ScopeInterface::SCOPE_STORE
    );
    $cond = [
        '{{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) . ')'
    );
}

Answer 2

Ah, I wish I had time to actually polish this, but this will have to do for now.

The following will work on a collection with only configurable products, I haven't tested its behavior on a collection with both configurables + simples, it probably wouldn't go too well.

The method is basically:

1) LEFT join a configurable's children (this makes a configurable show N times in the result query, where N is the amount of its children)

2) INNER join the children's qty from cataloginventory_stock_item on the condition that qty > 0, effectively filtering out configurables with children only out of stock

3) GROUP by configurable entity_id (returning basically 1 instance of each configurable)

Here's the relevant code, which can be applied to any collection:

$collection->joinField(
                  'links',
                  'catalog_product_super_link',
                  'product_id',
                  'parent_id=entity_id',
                  null,
                  'left'
                );
$collection->joinField(
                    'item',
                    'cataloginventory_stock_item',
                    '*',
                    'product_id=links',
                    '{{table}}.qty > 0',
                    'inner'
                );
$collection->groupByAttribute('entity_id');