Magento: Get a collection of order items for a product collection filtered by attribute

Question

I am working on a category summary report for the Magento store (1.6).

To this end, I want to get a collection of order items for a subset of products — that product, a unique category identifier (which creates the Magento product attribute that I created).

I can get the appropriate result set by basing the collection on a catalog / product.

$collection = Mage::getModel('catalog/product') ->getCollection() ->addAttributeToFilter('unique_category_id', '75') ->joinTable('sales/order_item', 'product_id=entity_id', array('price'=>'price','qty_ordered' => 'qty_ordered')); 

He doesn't like Magento, because there are duplicate entries for the same product ID.

How can I generate code to get this result set based on order items? Merging into a product collection, filtered by attribute, eludes me. This code does not do the trick, since it assumes that the attribute is in the order item and not in the product.

 $collection = Mage::getModel('sales/order_item') ->getCollection() ->join('catalog/product', 'entity_id=product_id') ->addAttributeToFilter('unique_category_id', '75'); 

Any help is appreciated.

Answer 1

The only way to do cross-entity work is clean and efficient by building SQL using a collection selection object.

 $attributeCode = 'unique_category_id'; $alias = $attributeCode.'_table'; $attribute = Mage::getSingleton('eav/config') ->getAttribute(Mage_Catalog_Model_Product::ENTITY, $attributeCode); $collection = Mage::getResourceModel('sales/order_item_collection'); $select = $collection->getSelect()->join( array($alias => $attribute->getBackendTable()), "main_table.product_id = $alias.entity_id AND $alias.attribute_id={$attribute->getId()}", array($attributeCode => 'value') ) ->where("$alias.value=?", 75); 

It's good for me. I tend to skip the full path of joining the eav_entity_type table, then eav_attribute , then the value table, etc. For performance reasons. Since attribute_id is an entity, this is all that is needed.
Depending on the area of ​​your attribute, you may need to add a store identifier.