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?


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>";
| improve this answer | |
  • this one gives me products that has children – Anna Mar 30 '17 at 15:10
  • Check updated answer, You only need to replace = with != :) – Jaimin Sutariya Mar 30 '17 at 15:36
  • I have thousands of products in the system - I can't use join with "not equal" – Anna Mar 31 '17 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');
| improve this answer | |
  • If it worked for you, please mark it as correct so it can be useful to others. – Jaimin Sutariya Mar 31 '17 at 13:36
  • It did the trick for me. Found 2447 products :) – Damodar Bashyal Aug 1 '19 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
| improve this answer | |
  • This also works for Magento 2, thanks! – Akif Feb 20 at 13:21

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.