6

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?

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

3 Answers 3

8
+50

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();
}
3
  • Nice to see one Magento1 old school sample script in Magento2 :D Dec 29, 2017 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, 2017 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, 2019 at 15:21
3

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.

4
  • 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, 2017 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? Dec 27, 2017 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, 2017 at 19:51
  • one of the only things in common is the use of the attribute pos_maat
    – Ronny
    Dec 28, 2017 at 18:10
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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