Magento – How to get a collection of attribute values and products that use them

attributescollection-filteringcollection;eavreports

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()
        ->addAttributeToSelect($attributeId)
        ->groupByAttribute($attributeId)
        ->addExpressionAttributeToSelect('instances', "COUNT({{entity_id}})", 'entity_id')
        ->setOrder($attributeId);

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')
        ->setAttributeFilter($attribute->getId())
        ->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?

Best Answer

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.

Code

$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
$valuesCollection->getSelect()
    ->joinLeft(
        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')
    ->reset(Zend_Db_Select::COLUMNS)
    ->columns(array('main_table.option_id', new Zend_Db_Expr('COUNT(value_table.entity_id)')))
    ->where('main_table.attribute_id=:attribute_id')
    ->group('main_table.option_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]

Related Topic