dConstructing

Custom product attributes and flat database tables in Magento

Magento Improvements

I recently started using flat product tables in Magento. It drastically sped up the site load time and I could never go back (to learn how to enable flat product and category tables, visit the following link and read Tip 4 - http://blog.nexcess.net/2010/10/22/6-tips-for-making-magento-production-ready/)

When I did that, I noticed some product attributes that should be displayed on various pages suddenly stopped showing up. Often times, in the attribute value's place was simply the word "No". I learned that in order for custom attributes to appear on the site when using flat product tables you have to indicate that the attribute is "Used in Product Listing" (to learn how to indicate an attribute is "Used in Product Listing", visit the following link - http://stackoverflow.com/questions/6271284/can-i-add-other-attributes-to-magentos-flat-product-catalog-table).

The Problem

A while ago I built a module that included a custom product attribute. The attribute triggered some design changes on the frontend of the site, but those changes weren't in place any more. The attribute was listed on the Manage Attributes page of the site admin and the attribute was marked "Used in Product Listing", but the data didn't seem to exists. The site worked fine if I switched flat tables off, but not if flat tables were being used.

Collecting Data

I added some debug code to my module to see what Magento returned when I retrieved my custom attribute from product. Nothing was returned. It's like it didn't exist.

I found the flat product table in the database and got a list of all the columns in the table. Here's the MySQL query I ran to get that list of columns (the number 1 in the query could be different in your database depending on what your store id is set to. Most often it will be 1):

describe catalog_product_flat_1;

Each of the other product attributes had its own column, but my custom attribute was not included on the list. I assumed my custom attribute should have its own colum too, so I started looked for the code that build the flat product table.

Breakthrough

I'll admit I never did find the code that built the flat product table. What I did find was an interestingly named method in one of the classes for my custom attribute.

public function getFlatColums()
{
    return array();
}

I built my module back when I was pretty new with Magento, so when it came time to create a product attribute I basically copied an existing product attribute and changed some of the strings to say what I wanted them to say. I had no idea what this method was for at the time, but I'm glad it got copied over. I did some comparisons between this attribute, some Magento default attributes, and some attributes that had been created using the Manage Attributes page of the site admin.

What I now know

Each attribute has what is called a source model. The source model is used to configure the attribute's options. For instance, if the attribute is displayed as a dropdown list, the source model determine's what gets listed as dropdown options.

Each attributes source model is specified in the database in the eav_attribute table. It's a long list, so you'll have some fun searching through it unless you have an identifier like the attribute_id or the attribute_code.

select attribute_id,attribute_code,frontend_label,source_model from eav_attribute where attribute_id=975;

+--------------+-----------------------+------------------+----------------------------------------+
| attribute_id | attribute_code        | frontend_label   | source_model                           |
+--------------+-----------------------+------------------+----------------------------------------+
|          975 | custom_attribute_code | Custom Attribute | catalog/product_attribute_source_model |
+--------------+-----------------------+------------------+----------------------------------------+

In the example above, the source model shown would translate to this file:

Catalog/Model/Product/Attribute/Source/Model.php

The source model is what contains the getFlatColums method I mentioned earlier. The getFlatColums method is what tells the indexer what columns to add to the flat table. Because my method was returning an empty array, no columns were being added. I updated my method to something like this:

public function getFlatColums()
{
    return array($this->getAttribute()->getAttributeCode() => array(
        'type'      => 'tinyint',
        'unsigned'  => true,
        'is_null'   => true,
        'default'   => null,
        'extra'     => null
    ));
}

With that in place, a column should get added to the flat table when your reindex the Product Flat Data. Sadly, the data in that column won't be populated. To make sure data is added to that column, you'll need to add this method to your source model:

public function getFlatUpdateSelect($store)
{
    return Mage::getResourceSingleton('eav/entity_attribute')
        ->getFlatUpdateSelect($this->getAttribute(), $store);
}

I'm not exactly sure what it does, but now when you reindex the Product Flat Data, your custom attribute will have its own column, and the column data will be filled correctly for each row on the table.

In Short

If your custom product attribute is not working with your flat product tables:

  1. Make sure your attribute is set to be "Used in Product Listing"
  2. Add/build out the getFlatColums method in the attribute's source model if the attribute doesn't have a column on the catalog_product_flat_1 table
  3. Add/build out the getFlatUpdateSelect method in the attribute's source model if the attribute data isn't populating the attribute column on the catalog_product_flat_1 table

I hope that helps.