Magento2 - How to see if configurable products has simple products attached

Question

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?

Answer 1

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();
}

Answer 2

If you are comfortable with using MySQL to answer your question, you could write a query such as

select * from catalog_product_entity left join catalog_product_relation on (parent_id = entity_id) where type_id = "configurable" group by parent_id having (count(*) = 0)

This will join the table for products with the table that houses the parent/child relationship. Then filtering that down via a having clause to find all the ones without that relationship.

Answer 3

Tested in Magento Commerce 2.4.1

This will give the configurable products without any child assigned.

Note: catalog_product_super_link table keeps the relationship of the parent to child for configurable products and it uses the row_id field to map, NOT the entity_id.

select *, count(sl.parent_id)
from catalog_product_entity e
left join catalog_product_super_link sl on sl.parent_id = e.row_id
where e.type_id = 'configurable'
group by sl.parent_id
having count(sl.parent_id) = 0;

Similarly...

This will give you a list of simple products that don't have any parent/child mapping with a configurable product, using a simple left join.

select * from catalog_product_entity e
left join catalog_product_super_link sl on sl.product_id = e.row_id
where sl.product_id is null
and e.type_id = 'simple';