Can I add other attributes to magento's flat product catalog table?

Question

I am in the process of optimizing magento store, and I've run across a couple of posts that recommend using the Flat Product Catalog for stores with a large amount of SKUs.

As I have over 10K products I thought I'd give it a try. However, when using the Flat Product Catalog only a select few attributes are loaded in product object (e.g. SKU, name, short description). And my template displays a few other attributes in the search/browse view, such as Manufacturer and Color.

Is there a way to add these attributes to the flat product catalog table so they too can be accessed?

Answer 1

1.4.x.x, just go into the attributes you want to be used in the "Flat Product Catalog" and make sure the property "Used in Product Listing" is set to Yes. Upon making changes, reindex "Flat Product Data"

The following properties cause the attribute to be included in the "Flat Product Catalog":

"Use in Layered Navigation" = Yes
"Used in Product Listing" = Yes
"Used for Sorting in Product Listing" = Yes

Answer 2

I have been working through this issue too, which I would describe as "cannot access product collection attribute in flat mode" or "addAttributeToSelect not working in flat mode".

I found a "clean" solution 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

Please note - in the code below I have used the associated product collection, but this applies to any product collection (specifically, anything inheriting from Mage_Eav_Model_Entity_Collection_Abstract)

Failing code:

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

In flat mode, the above code silently fails to add the attribute if it happens not to be in the flat table.

Working code:

$_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. It works even if this duplicates an attribute that's already in the flat table.

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

(tested in CE 1.6.2.0)