Filter Product Collection by Non-Flat Attribute in Magento

ce-1.7.0.2collection;

I am doing the following:

$productCollection = Mage::getModel('catalog/product')
    ->getCollection();

$productCollection
    ->addAttributeToFilter('my_attribute', 1);

my_attribute is not in the flat tables, but flat tables are enabled.

I keep getting the full collection.

The reason seems to be in \Mage_Catalog_Model_Resource_Product_Collection::addAttributeToSelect:

$columns = $this->getEntity()->getAttributeForSelect($attributeCode);

No $this->getEntity() is an instance of Mage_Catalog_Model_Resource_Product_Flat which fetches the flat fields – and if none is found, just returns null.

What is a clean way to add a non-flat attribute to the collection filter?

In my case it does not sense, to add the attribute to the flat table.

Best Answer

You could join the necessary table yourself.

$productCollection = Mage::getModel('catalog/product')
->getCollection();

$table = Mage::getSingleton('eav/config')->getAttribute('catalog_product', 'my_attribute')->getBackend()->getTable();
$attributeId = Mage::getSingleton('eav/config')->getAttribute('catalog_product', 'my_attribute')->getAttributeId();

$productCollection->getSelect()->join(array('attributeTable' => $table), 'e.entity_id = attributeTable.entity_id', array('my_attribute' => 'attributeTable.value'))
                            ->where("attributeTable.attribute_id = ?", $attributeId)
                            ->where("attributeTable.value = ?", 1);

You might want to join by store_id, too.

Related Topic