How to get enabled configurable products which has associate products without inventory? [Magento 2]

My Query is: I want to get enabled configurable products which has associate products without inventory OR child products of enabled config products have inventory zero.

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/317553/how-to-get-enabled-configurable-products-which-has-associate-products-without-in