Use below code for filter out of stock Simple/Config products [magento 2 out of stock enabled configurable products] [closed]

Use below code for filter out of stock Simple/Config products [magento 2 out of stock enabled configurable products]

$objectManager = $bootstrap->getObjectManager();

$state = $objectManager->get('Magento\Framework\App\State');

$state->setAreaCode('frontend');

$productCollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\CollectionFactory');

$collection = $productCollection->create();

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

$collection =
        $collection->addAttributeToSelect('*')
->addAttributeToSort('created_at', 'DESC')
->joinField('qty',
        'cataloginventory_stock_item',
        'qty',
        'product_id=entity_id',
        '.stock_id=1',
        'left'
    )->joinTable('cataloginventory_stock_item', 'product_id=entity_id', ['stock_status' => 'is_in_stock'])
    ->addAttributeToSelect('stock_status')
    ->addFieldToFilter('stock_status', ['eq' => 0])
    ->addAttributeToFilter('status', \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED)
    ->addAttributeToFilter('type_id', ['eq' => 'configurable'])->load();

foreach ($collection as $product) {
    echo 'Config Product SKU: '.$product->getSku()."\n\n";
}

Note: Change $collection->addAttributeToFilter('type_id', ['eq' => 'configurable']) as per your requirements.

MySQL Query as follows:

SELECT e.*, at_qty.qty, cataloginventory_stock_item.is_in_stock AS stock_status, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS status FROM catalog_product_entity AS e LEFT JOIN cataloginventory_stock_item AS at_qty ON (at_qty.product_id=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN cataloginventory_stock_item ON (cataloginventory_stock_item.product_id=e.entity_id) INNER JOIN catalog_product_entity_int AS at_status_default ON (at_status_default.row_id = e.row_id) AND (at_status_default.attribute_id = '94') AND at_status_default.store_id = 0 LEFT JOIN catalog_product_entity_int AS at_status ON (at_status.row_id = e.row_id) AND (at_status.attribute_id = '94') AND (at_status.store_id = 14) WHERE ((cataloginventory_stock_item.is_in_stock = 0) AND (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1') AND (e.type_id = 'configurable')) AND (e.created_in <= '1589313000') AND (e.updated_in > '1589313000') ORDER BY e.created_at DESC;

from Active questions tagged magento-enterprise - Magento Stack Exchange

Click link to view all answer https://magento.stackexchange.com/questions/317426/use-below-code-for-filter-out-of-stock-simple-config-products-magento-2-out-of