I would like to run a report of attribute values used by a drop-down select attribute, e.g. colour, and the quantity of products that actually use them.

I can get the list of actually used attributes and their respective counts by using:

    $productCollection = Mage::getModel('catalog/product')->getCollection()
        ->addExpressionAttributeToSelect('instances', "COUNT({{entity_id}})", 'entity_id')

I can also get a complete list of values for a given attribute:

    $attribute = Mage::getModel('eav/entity_attribute')->loadByCode(Mage_Catalog_Model_Product::ENTITY, $attributeId);

    $valuesCollection = Mage::getResourceModel('eav/entity_attribute_option_collection')
        ->setStoreFilter(0, false);

However, I have so far not been able to join these collections or find a better way of achieving what I want. In my list I would like the complete set of attribute values including the ones that are not used by any products. Furthermore, in my collection, I would also like the sort order numbers assigned to each attribute value. Plus the product count.

Any ideas?


1 Answer 1


including the ones that are not used by any products.

Then you need your attribute collection and left join the products. Left join is not supported by the collection join() method, so use the underlying Zend_Db_Select.


$attributeCode = 'color';

$attribute = Mage::getModel('eav/entity_attribute')->loadByCode(Mage_Catalog_Model_Product::ENTITY, $attributeCode);
$productResource = Mage::getResourceModel('catalog/product');

// Use collection only to prepare query
$valuesCollection = Mage::getResourceModel('eav/entity_attribute_option_collection')
    ->setStoreFilter(0, false);

// Now refine query
        array('value_table' => $productResource->getTable(array('catalog/product', 'int'))),
        'main_table.option_id=value_table.value AND main_table.attribute_id=value_table.attribute_id', 'entity_id')
    ->columns(array('main_table.option_id', new Zend_Db_Expr('COUNT(value_table.entity_id)')))

// ...and fetch data
$result = $productResource->getReadConnection()->fetchPairs(
    $valuesCollection->getSelect(), array('attribute_id' => $attribute->getId()));

Result now contains an array in the form [option_id => number of products]

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.