In one of my blocks I want to get a product collection including items that are out of stock, but the product collection always includes WHERE (stock_status_index.stock_status = 1) if you are managing stock.

I don't want out of stock items to show up in categories and search so I do want to manage stock, but in one instance I want to be able to get the products that are out of stock as well.

I have tried all solutions I could find, including:
->joinField('stock_item', 'cataloginventory_stock_item', 'is_in_stock', 'product_id=entity_id', 'is_in_stock=0')


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

$cond[] = '{{table}}.use_config_manage_stock = 1';

    '(' . join(') OR (', $cond) . ')'

But everything is negated by the WHERE (stock_status_index.stock_status = 1) part.

EDIT: $collection->getAllIds() does return the IDs of out of stock products, so I can use that to get the out of stock products via a ProductRepository::getById.

  • Did you ever find solution on this? I am also facing this issue right now Commented Feb 19, 2019 at 9:56

3 Answers 3


I was facing same issue, but got the solution on git hub forum:

I used this code and it's working fine now. It's a trick, but it worked for me

$collection = $this->_productCollectionFactory->create()
//**applied below process to load disable products with default 'enable' once.
$where = $collection->getSelect()->getPart('where');
foreach ($where as $key => $condition)
     if(strpos($condition, 'stock_status_index.stock_status = 1') !== false){
             $updatedWhere[] = '(stock_status_index.stock_status IN (1,0))';
      } else {
              $updatedWhere[] = $condition;
 $collection->getSelect()->setPart('where', $updatedWhere);

Reference: https://github.com/magento/magento2/issues/13411#issuecomment-361250582


try this

     * @var \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory
    protected $productCollectionFactory;

     * @var \Magento\Catalog\Model\Product\Attribute\Source\Status
    protected $productStatus;

     * @var  \Magento\Catalog\Model\Product\Visibility
    protected $productVisibility;

     * @var \Magento\Catalog\Model\ProductFactory
    protected $_productFactory;

     * @param \Magento\Framework\View\Element\Template\Context $context
     * @param \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory
     * @param \Magento\Catalog\Model\ProductFactory $productFactory
     * @param \Magento\Catalog\Model\Product\Attribute\Source\Status $productStatus
     * @param \Magento\Catalog\Model\Product\Visibility $productVisibility
     * @param array $data
    public function __construct(
        \Magento\Framework\View\Element\Template\Context $context,
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,
        \Magento\Catalog\Model\ProductFactory $productFactory,
        \Magento\Catalog\Model\Product\Attribute\Source\Status $productStatus,
        \Magento\Catalog\Model\Product\Visibility $productVisibility,
        array $data = []
    ) {
        $this->productCollectionFactory = $productCollectionFactory;
        $this->_productFactory = $productFactory;
        $this->productStatus = $productStatus;
        $this->productVisibility = $productVisibility;
        parent::__construct($context, $data);


And the Filter should be

//It will return only out of stock

$productCollection = $this->productCollectionFactory
                ->addAttributeToSort('status', $asc_desc)
                ->joinField('stock_item', 'cataloginventory_stock_item', 'is_in_stock', 'product_id=entity_id', 'is_in_stock=0')

//It will return other than Out of Stock Items

$productCollection = $this->productCollectionFactory
                ->addAttributeToSort('status', $asc_desc)
                ->joinField('stock_item', 'cataloginventory_stock_item', 'is_in_stock', 'product_id=entity_id', 'is_in_stock=1')
  • 1
    That doesn't return anything because Magento still adds WHERE (stock_status_index.stock_status = 1) to the end of the query used to load the product collection when managing stock Commented Oct 10, 2017 at 8:11

Here magento2 added this. Feel free to change it

<type name="Magento\Catalog\Model\ResourceModel\Product\Collection">
        <plugin name="add_stock_information" type="Magento\CatalogInventory\Model\AddStockStatusToCollection" />

You can always disable it in your di.xml using this

plugin disabled: To disable a plugin, set this element to true. The default value is false. Use this property to disable core or third-party plugins in your di.xml file.


Here is the file


You can override and remove that.


Even better there is a config option in magento 2.2

configuration->catalog->inventory->Display out of stock products

Your Answer

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

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