Magento:获取按属性过滤的产品集合的订单项集合

问题描述：

我正在为Magento(1.6)商店开发类别汇总报告.

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

为此，我想获取一部分产品的订单项集合-这些产品的唯一类别ID(这是我创建的Magento产品属性)与特定值匹配.

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不喜欢它，因为存在相同商品ID的重复条目.

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

感谢您的帮助.

答

使跨实体选择有效且唯一地工作的唯一方法是通过使用集合选择对象构建SQL.

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

这对我来说很好.由于性能原因，我倾向于跳过完整的方式来加入eav_entity_type表，然后是eav_attribute，然后是值表.由于attribute_id是特定于实体的，因此仅需这些.
根据属性的范围，您可能还需要添加商店ID.

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.

Magento:获取按属性过滤的产品集合的订单项集合

相关推荐