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

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);
        $joinTable = $collection->getTable('customtable_product');
        ->join($joinTable.' as product','e.entity_id = product.product_id and product.product_status = 1',array('*'));
        '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


2 Answers 2


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

\Magento\CatalogInventory\Helper\Stock $stockHelper


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(
    $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';

        '(' . join(') OR (', $cond) . ')'
  • This code works only with simple products. It is not working with configurable product. Jan 5, 2017 at 3:49
  • Do you have test with latest Magento 2.1.3 version ? Jan 5, 2017 at 8:49
  • No, I am working with 2.1.2 Jan 5, 2017 at 10:54
  • I know that there is some stock related issue with Magento 2.1.2 and 2.1.3, I cannot help you more, try to read Release Notes. Jan 9, 2017 at 13:06

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:

                    '{{table}}.qty > 0',
  • can you kindly update yourr answer? Oct 6, 2022 at 9:00

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.