Filter product collection by non-flat attribute

Question

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.

Answer 1

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.

Answer 2

A hack (CE 1.6.2.0+) is to pass the condition as an array and believe it or not this works as intended:

$collection->addFieldToFilter(array(array('attribute' => 'my_attribute', 'eq' => 1)));

Answer 3

The reason ColinM's answer works is due to the code in app/code/core/Mage/Catalog/Model/Resource/Product/Collection.php's addAttributeToFilter method. If you use this array format, it doesn't call addAttributeToSelect. In flat mode, addAttributeToSelect silently fails if the attribute is not in the flat table.

(below is a re-hash of my answer on https://stackoverflow.com/questions/6271284/can-i-add-other-attributes-to-magentos-flat-product-catalog-table/17021620 - I'm not sure what the etiquette is for that but know I would've found it helpful)

I wanted a "clean" solution for flat-mode collection selecting and filtering on non-flat attributes, which:

• does not require the attribute to have specific settings in admin (it might be added by a user, or hidden on the front end)
• works for both flat and non-flat mode

I used the associated product collection, but this applies to any EAV collection.

Failing code:

$_product = Mage::getModel('catalog/product')->loadByAttribute( 'sku', 'ABC123' );
$coll = $_product->getTypeInstance()->getAssociatedProductCollection()
    ->addAttributeToSelect( 'my_custom_attribute' )
    ->addAttributeToFilter( 'my_custom_attribute', 3 )
;

In flat mode, the above code silently fails to select or filter on the attribute if it happens not to be in the flat table.

Adding to the select:

$_product = Mage::getModel('catalog/product')->loadByAttribute( 'sku', 'ABC123' );
$coll = $_product->getTypeInstance()->getAssociatedProductCollection()
    ->joinAttribute( 'my_custom_attribute', 'catalog_product/my_custom_attribute', 'entity_id', null, 'left' )
    ->addAttributeToSelect( 'my_custom_attribute' )
;

The joinAttribute method adds a join to the query for the specific attribute requested. It still works when the attribute is already in the flat table, but will be slightly less efficient than purely using the flat table.

I've used a left join there, to ensure that it fetches products if my_custom_attribute is not set on those products. Change that for inner if you're only interested in rows where my_custom_attribute is set.

Adding to the filter (as per ColinM above):

$_product = Mage::getModel('catalog/product')->loadByAttribute( 'sku', 'ABC123' );
$coll = $_product->getTypeInstance()->getAssociatedProductCollection()
    ->addAttributeToFilter( array( array( 'attribute' => 'my_custom_attribute', 'eq' => 3 ) ) )
;

The above code will add it to the select as well as obeying your filter.

(tested in CE 1.6.2.0)

Answer 4

In the Mage_Rss module they used the a hacky-method to disable the flat tables. They use the fact, that flat tables are always off in the admin store and so just emulate the admin store.

class Mage_Rss_Helper_Data {

[...]

/**
 * Disable using of flat catalog and/or product model to prevent limiting results to single store. Probably won't
 * work inside a controller.
 *
 * @return null
 */
public function disableFlat()
{
    /* @var $flatHelper Mage_Catalog_Helper_Product_Flat */
    $flatHelper = Mage::helper('catalog/product_flat');
    if ($flatHelper->isEnabled()) {
        /* @var $emulationModel Mage_Core_Model_App_Emulation */
        $emulationModel = Mage::getModel('core/app_emulation');
        // Emulate admin environment to disable using flat model - otherwise we won't get global stats
        // for all stores
        $emulationModel->startEnvironmentEmulation(0, Mage_Core_Model_App_Area::AREA_ADMINHTML);
    }
}

After starting the emulation you should reset it with emulationModel->stopEnvironmentEmulation()

Answer 5

when you create the attribute it should be on the Global level and filterable. This way it will be usable in the layared navigation. Also it will require the attribute to be a dropdown or multiselect. I would personally advice against changing the core files to fit your needs in this case