I checked into this and got my nearest answer but not as we usually expecting from magento.

I want all out of stock product which can visible in store front means have respective product landing page (PDP). which means exclude Not Visible Individually and enable products.

2 Answers 2


After doing R&D found a solution to get all the out of stock products


namespace [VENDOR_NAME]\[MODULE_NAME]\Block;

use Magento\Catalog\Model\Product\Attribute\Source\Status;
use Magento\Catalog\Model\Product\Visibility;
use Magento\Catalog\Model\ResourceModel\Product\CollectionFactory;
use Magento\Framework\View\Element\Template;
use Magento\Framework\View\Element\Template\Context;

class OoSProducts extends Template

     * @var CollectionFactory
    protected $_productCollection;

     * @var Visibility
    protected $_productVisibility;

     * @var Status
    protected $_productStatus;

     * OoSProducts constructor.
     * @param Context $context
     * @param CollectionFactory $productCollection
     * @param Visibility $productVisibility
     * @param Status $productStatus
    public function __construct(
        Context $context,
        CollectionFactory $productCollection,
        Visibility $productVisibility,
        Status $productStatus
    )  {

        $this->_productCollection = $productCollection;
        $this->_productVisibility = $productVisibility;
        $this->_productStatus = $productStatus;


     * Out of Stock Product Collection
     * @return $this|\Magento\Catalog\Model\ResourceModel\Product\Collection
    public function getOutofstockProductCollection()
        $collection = $this->_productCollection->create();

        $collection->setFlag('has_stock_status_filter', true);

        $collection = $collection->addAttributeToSelect(array('name', 'sku', 'product_url'))
            ->addAttributeToSort('name', 'ASC')
            ->joinTable('cataloginventory_stock_item', 'product_id=entity_id', array('stock_status' => 'is_in_stock'))
            ->addFieldToFilter('stock_status', ['eq' => 0])
            ->addAttributeToFilter('status', ['in' => $this->_productStatus->getVisibleStatusIds()])
        return $collection;

pt 1. To exclude product visibility Not Visible Individually, use this method.

$this->_productVisibility->getVisibleInSiteIds()  returns the 


pt 2. Get all enabled product, Use below method

$this->_productStatus->getVisibleStatusIds() returns


pt 3. $collection->setFlag('has_stock_status_filter', true);

It is important to set the has_stock_status_filter flag to true to get proper results for Out of stock products.

Note: [VENDOR_NAME]\[MODULE_NAME] should be replaced by your project directory names i.e vendor name and its module name.


Try this code In your collection

$select = $collection->getSelect();

    if (false === strpos($select->__toString(), 'cataloginventory_stock_status')) {
        $website = $this->_storeManager->getWebsite();
            ->addStockStatusToSelect($select, $website);


    $field = 'salable desc';

    $field = new \Zend_Db_Expr('IF(stock_status_index.qty > 0, 0, 1)');


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.