Getting disabled products in a collection for Magento 2.3.1

Question

I have the following code to get all disabled products

    $products = $this->productCollectionFactory->create()
        ->addAttributeToSelect('sku')
        ->addAttributeToSelect('name')
        ->addAttributeToFilter('status', \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_DISABLED);

However, the result is always coming back empty even though in the admin I can see there are disabled products.

Therefore my question is how do I get all disabled products in a collection?

Answer 1

This works for me

$products = $this->productCollectionFactory->create()
            ->addAttributeToSelect('sku')
            ->addAttributeToSelect('name')
            ->addAttributeToSelect('status')
            ->load();

        $products->clear();
        $fromAndJoin = $products->getSelect()->getPart('FROM');
        foreach ($fromAndJoin as $key => $index) {
            if ($key == 'stock_status_index') {
                $index['joinType'] = 'left join';
            }
            $updatedfromAndJoin[$key] = $index;
        }
        $products->getSelect()->setPart('FROM', $updatedfromAndJoin);

        $where = $products->getSelect()->getPart('where');
        foreach ($where as $key => $condition) {
            if (strpos($condition, 'stock_status_index.stock_status = 1') == false) {
                $updatedWhere[] = $condition;
            }
        }
        $products->getSelect()->setPart('where', $updatedWhere);
        $products->load();

The answer was copied from https://community.magento.com/t5/Magento-2-x-Technical-Issues/How-to-get-disable-products-in-product-collection-in-magento2-2/td-p/85581