In magento 1.9 I need to get collection of all configurable products that have no associated products. The first idea is to get all configurable and then in loop check each for its children. But maybe there is more elegant way?

3 Answers 3


You can use below code to get all configurable products which does not have any associated products.

$collection = Mage::getModel("catalog/product")->getCollection()->addAttributeToSelect("*")->addAttributeToFilter('type_id', 'configurable');
        'entity_id != catalog_product_super_link.parent_id',
foreach($collection as $product){
    echo $product->getName()."<br>";
  • this one gives me products that has children
    – Anna
    Mar 30, 2017 at 15:10
  • Check updated answer, You only need to replace = with != :) Mar 30, 2017 at 15:36
  • I have thousands of products in the system - I can't use join with "not equal"
    – Anna
    Mar 31, 2017 at 12:25

Ok, I used catalog_product_super_link in collection query, as suggested. This one that works for me:

  $collection = Mage::getModel("catalog/product")
            ->addAttributeToFilter('type_id', 'configurable');
                        'e.entity_id = catalog_product_super_link.parent_id',
    $collection->getSelect()->where('catalog_product_super_link.parent_id IS NULL');
  • If it worked for you, please mark it as correct so it can be useful to others. Mar 31, 2017 at 13:36
  • It did the trick for me. Found 2447 products :) Aug 1, 2019 at 0:45

with SQL Query:

    entity_id, type_id, sku, cpsl.product_id
    catalog_product_entity cpe
        LEFT JOIN
    catalog_product_super_link cpsl ON cpsl.parent_id = cpe.entity_id
    cpe.type_id = 'configurable'
        AND cpsl.parent_id IS NULL
  • This also works for Magento 2, thanks!
    – Akif
    Feb 20, 2020 at 13:21

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.