Magento: Get Collection of Order Items for a product collection filtered by an attribute

Question

I'm working on developing a category roll-up report for a Magento (1.6) store.

To that end, I want to get an Order Item collection for a subset of products - those product whose unique category id (that's a Magento product attribute that I created) match a particular value.

I can get the relevant result set by basing the collection on 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'));

Magento doesn't like it, since there are duplicate entries for the same product id.

How do I craft the code to get this result set based on Order Items? Joining in the product collection filtered by an attribute is eluding me. This code isn't doing the trick, since it assumes that attribute is on the Order Item, and not 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 make cross entity selects work cleanly and efficiently is by building the SQL with the collections select 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);

This works quite well for me. I tend to skip going the full way of joining the eav_entity_type table, then eav_attribute, then the value table etc for performance reasons. Since the attribute_id is entity specific, that is all that is needed.
Depending on the scope of your attribute you might need to add in the store id, too.