1

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

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');
$collection->getSelect()->join(
        'catalog_product_super_link',
        'entity_id != catalog_product_super_link.parent_id',
        array('parent_id')
    )->group('parent_id');
foreach($collection as $product){
    echo $product->getName()."<br>";
}
3
  • this one gives me products that has children
    – Anna
    Mar 30 '17 at 15:10
  • Check updated answer, You only need to replace = with != :) 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
1

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

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

with SQL Query:

SELECT 
    entity_id, type_id, sku, cpsl.product_id
FROM
    catalog_product_entity cpe
        LEFT JOIN
    catalog_product_super_link cpsl ON cpsl.parent_id = cpe.entity_id
WHERE
    cpe.type_id = 'configurable'
        AND cpsl.parent_id IS NULL
1
  • This also works for Magento 2, thanks!
    – Akif
    Feb 20 '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.