Which attributes go into the catalog_product_flat* tables?

Question

In app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Flat/Indexer.php (Magento 1.5) or in app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php in later versions I believe:

The public function getAttributesCodes() retrieves the attribute codes that will go into the flat table. Here is the WHERE condition:

$whereCond  = array(
                $this->_getReadAdapter()->quoteInto('main_table.backend_type=?', 'static'),
                $this->_getReadAdapter()->quoteInto('additional_table.used_in_product_listing=?', 1),
                $this->_getReadAdapter()->quoteInto('additional_table.used_for_sort_by=?', 1),
                $this->_getReadAdapter()->quoteInto('main_table.attribute_code IN(?)', $this->_systemAttributes)
            );
            if ($this->getFlatHelper()->isAddFilterableAttributes()) {
                $whereCond[] = $this->_getReadAdapter()->quoteInto('additional_table.is_filterable>?', 0);
            }

Clearly, certain attribute types are always added to the table. Static, used_in_product_listing=1, used_for_sort_by=1, and the attributes listed in $this->_systemAttributes (status, required_options, tax_class_id, weight).

The `if statement is where I'm stuck. Are filterable attributes added or not? How to determine if they are or not?

I found this file: app/code/core/Mage/Catalog/Helper/Product/Flat.php

It has a class constant: const XML_NODE_ADD_FILTERABLE_ATTRIBUTES = 'global/catalog/product/flat/add_filterable_attributes';

It's used in this function:

public function isAddFilterableAttributes()
    {
        return intval(Mage::getConfig()->getNode(self::XML_NODE_ADD_FILTERABLE_ATTRIBUTES));
    }

What does it all mean? How do I know if filterable attributes will be added? What determines it? What changes it?

Answer 1

If you look in Mage/Catalog/etc/config.xml you will find this line:

<add_filterable_attributes>0</add_filterable_attributes>

This means that the filterable attributes are not added to the flat tables by default.
If you change that value to 1, you should see the filterable attributes in the flat tables.

You can change it for test purposes directly in the core file, but don't leave it like that if you decide that you need it.

In order to change it the proper way, build your own module that does it.
You will need 2 files for that.

app/etc/modules/[Namespace]_[Module].xml - the declaration file

<?xml version="1.0"?>
<config>
    <modules>
        <[Namespace]_[Module]>
            <codePool>local</codePool>
            <active>true</active>
            <depends>
                <Mage_Catalog /> <!-- must depend on the catalog module so its config is loaded after the config of the catalog -->
            </depends>
        </[Namespace]_[Module]>
    </modules>
</config>

app/code/local/[Namespace]/[Module]/etc/config.xml - the configuration file where youo override the config setting

<?xml version="1.0"?>
<config>
    <modules>
        <[Namespace]_[Module]>
            <version>1.0.0</version>
        </[Namespace]_[Module]>
    </modules>
    <global>
        <catalog>
            <product>
                <flat>
                    <add_filterable_attributes>1</add_filterable_attributes> <!-- this should override the default setting -->
                </flat>
            </product>
        </catalog>
    </global>
</config>

Clear the cache and reindex. You should see the result in the flat tables.