Magento 2 – Check if Configurable Products Have Simple Products Attached

configurable-productmagento-2.1simple-product

We are using magento 2.1.9ce

And most of our products are made with an api.
There was a bug in this api connection, which disconnected simple products from the configurable product.

So now there are configurable products in our backend without any childs.
But we have 15K products in total, so to check them manually isn't an option.

Is there a way to see which configurable products don't have a simple product connected?

Best Answer

Try below script from magento2 root:

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

use Magento\Framework\App\Bootstrap;

try{

    require __DIR__ . '/app/bootstrap.php'; 

    $bootstrap = Bootstrap::create(BP, $_SERVER);

    $obj = $bootstrap->getObjectManager();
    $resource = $obj->get('Magento\Framework\App\ResourceConnection');
    $connection = $resource->getConnection();

    $sql = "SELECT * FROM catalog_product_entity WHERE type_id = 'configurable' and entity_id NOT IN (SELECT parent_id FROM catalog_product_relation)";
    $result = $connection->fetchAll($sql);

    if (!count($result)) {
        $sql = "SELECT * FROM catalog_product_entity WHERE type_id = 'configurable' and entity_id NOT IN (SELECT parent_id FROM catalog_product_super_link)";
        $result = $connection->fetchAll($sql);
    }

    echo "Number of products: " . count($result) . "<br/>";
    echo '<pre>';print_r($result);

} catch (Exception $e) {
    echo $e->getMessage();
}
Related Topic