Get all configurable products without any associated products

Question

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?

Answer 1

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>";
}

Answer 2

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

Answer 3

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