Category Attributes, Setup Script and Flat Tables

Question

I have created a setup script that creates new category attributes.

It works quite well, however after the setup did run, the flat tables are missing the columns and when the Magento API creates the select SQL queries, the Mysql server gives error because the column is not found:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.attribute_column_identifier' in 'field list', query was: SELECT `main_table`.`entity_id`, ...

Now I wonder what the common way is to establish it within the flat tables as well in the setup. I don't want to rebuild manually on the server.

• Raj: Is that a typo? "attribute_column_udentifier"?
• hakre: @Raj: Yes, a typo in the exemplary name. Thanks for pointing out.

Answer 1

Each of your custom attributes needs a source model with the following methods defined:

1. getFlatColums(): creates the value column on the flat tables
2. getFlatUpdateSelect(): populates the values

You can also specify an index for this column by defining getFlatIndexes().

For reference see Mage_Eav_Model_Entity_Attribute_Source_Boolean:

/**
 * Retrieve flat column definition
 *
 * @return array
 */
public function getFlatColums()
{
 $attributeCode = $this->getAttribute()->getAttributeCode();
 $column = array(
 'unsigned' => false,
 'default' => null,
 'extra' => null
 );
 if (Mage::helper('core')->useDbCompatibleMode()) {
 $column['type'] = 'tinyint(1)';
 $column['is_null'] = true;
 } else {
 $column['type'] = Varien_Db_Ddl_Table::TYPE_SMALLINT;
 $column['length'] = 1;
 $column['nullable'] = true;
 $column['comment'] = $attributeCode . ' column';
 }
 return array($attributeCode => $column);
}
/**
 * Retrieve Indexes(s) for Flat
 *
 * @return array
 */
public function getFlatIndexes()
{
 $indexes = array();
 $index = 'IDX_' . strtoupper($this->getAttribute()->getAttributeCode());
 $indexes[$index] = array(
 'type' => 'index',
 'fields' => array($this->getAttribute()->getAttributeCode())
 );
 return $indexes;
}
/**
 * Retrieve Select For Flat Attribute update
 *
 * @param int $store
 * @return Varien_Db_Select|null
 */
public function getFlatUpdateSelect($store)
{
 return Mage::getResourceModel('eav/entity_attribute')
 ->getFlatUpdateSelect($this->getAttribute(), $store);
}
/**
 * Get a text for index option value
 *
 * @param string|int $value
 * @return string|bool
 */
public function getIndexOptionText($value)
{
 switch ($value) {
 case self::VALUE_YES:
 return 'Yes';
 case self::VALUE_NO:
 return 'No';
 }
 return parent::getIndexOptionText($value);
}

• hakre: The field uses one of the standard ones, so I'm pretty sure these methods are there. What I want to prevent is to re-index manually to build the flat tables again.