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.

  • Hi sir ARE you KIND Enought to resolve Confusion ?? WAT is mean by non-flat attribute ? Thanks .and dont make magento Confusing .It is already confusing – Pratik C Joshi Oct 27 '14 at 11:37
  • I am talking about attributes which are not in the flat index. These are those with "Used in Product Listing" set to "No". – Alex Oct 28 '14 at 13:53
up vote 17 down vote accepted

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.

  • I think I would still have the problem that I do not get the products of all stores. (whenever I did not originally see this problem in my question). Looks like I want to fully disable the flat indices. – Alex Mar 20 '13 at 16:15
  • If you need all products, then flat tables won't be your friends, yes. – Matthias Zeis Mar 20 '13 at 16:19
  • I guess you may want to modify your question or accept my answer (which works for the original question), then. – Matthias Zeis Mar 21 '13 at 9:48
  • awasome.. concept – Amit Bera Sep 8 '14 at 14:07

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)));
  • Any clue why that works? – Alex May 15 '13 at 12:09
  • 3
    It works because for eav collection addFieldToFiler is a wrapper for addAttributeToFilter and this has an option to pass the attribute as an array: if (is_array($attribute)) { $sqlArr = array(); foreach ($attribute as $condition) { $sqlArr[] = $this->_getAttributeConditionSql($condition['attribute'], $condition, $joinType); } $conditionSql = '('.implode(') OR (', $sqlArr).')'; } – Marius Jun 26 '13 at 10:35
  • @Marius, does that mean it's not a hack? :P Can I feel good about using it? – Erfan Aug 18 '14 at 6:53
  • 3
    @Erfan. it's not a hack. it's a feature. – Marius Aug 18 '14 at 7:04
  • Sweet. It's still kinda odd the product table implementation (eav/flat) isn't abstracted away for things as simple as filtering a collection. Does this mean I always need to use addFieldToFilter instead of addAttributeToFilter in my code, because I won't know if it's using eav or flat? Whats the point of addAttributeToFilter anyway? – Erfan Aug 18 '14 at 8:46

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)

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

  • Where was this answer 3 days ago? ;_; – sg3s Sep 11 '13 at 12:58
  • Right here? Since Mar 20. – Alex Sep 11 '13 at 13:59

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

  • I do not want the attribute in the frontend filter - I simply want to do a collection filtering for internal use. – Alex Mar 20 '13 at 15:54

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.