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?

  • 2
    Look at @Pratik Oza - that worked perfectly for me. – dawhoo Dec 29 '17 at 21:45

Try below script from magento2 root:

ini_set('display_errors', 1);

use Magento\Framework\App\Bootstrap;


    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();
  • Nice to see one Magento1 old school sample script in Magento2 :D – Raul Sanchez Dec 29 '17 at 9:09
  • 2
    This works well for finding configurable products without children - Save above file as is, do not add closing php tag. Save as 'nochild.php'. Upload into root folder. Go to www.[yoursite.tld]/nochild.php and the output will list all configurable products without child products. – dawhoo Dec 29 '17 at 18:23
  • for Magento 2.3.2 I have had to replace entity_id with row_id. just in case it helps anyone! – willboudle Sep 19 '19 at 15:21

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.

  • 1
    is this the right query? Because it gives 0 results. And i can hardly imagine that there are no products disconnected – Ronny Dec 27 '17 at 19:39
  • You might actually get 0 results due to the fact that in M2 product types are fluid. In Magento 1 you had to decide at the start you were creating a configurable products. In M2, you can shift back and forth, for example adding configurations makes it configurable and removing them makes it simple. This being said, are there any patterns you can use to find the configurables, IE a certain type of SKU format? Can you look up some examples in your source data and reference them in Magento and see? – Stephen Fritz Dec 27 '17 at 19:45
  • the problem is that there are about 4-5K on unique configurable products. with all kind of sku's. And the api did make both configurable and simple products, but sometimes they where disconnected. thats why i want to have some kind of script for this – Ronny Dec 27 '17 at 19:51
  • one of the only things in common is the use of the attribute pos_maat – Ronny Dec 28 '17 at 18:10

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;


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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.