It’s not unusual to run into problems while working on a project. Or should I call them challenges? :) We had to create a product attribute with a very specific options list. This list was already defined as a business rule, so duplicating the list in the back-end was not a great idea. Our solution was to use the ‘source_model’ property for EAV attributes. Nothing too difficult so far, so what could have been our issue? Filtering the product collection after this attribute didn't work, even though products had the right value set.

I’m going to walk you through the steps we took in our implementation and through the solution we created for our issue.

Adding the custom attribute

While you can add as many custom attributes as you’d like from the backend, you can’t define a custom source for the options. And if you’re planning to use this attribute in custom code, you should definitely use installer scripts for adding it.

So, in an install script, we will have:

$productHelper = Mage::helper('catalog/product'); $setupModel = Mage::getModel('eav/entity_setup', 'core_setup'); $model = Mage::getModel('catalog/resource_eav_attribute'); $attributeLabel = 'Custom Options Label'; $attributeCode = 'special_options'; $attributeData = array( 'is_global' => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_WEBSITE, 'backend_model' => $productHelper->getAttributeBackendModelByInputType('multiselect'), 'frontend_model' => null, 'backend_type' => 'varchar', 'frontend_input' => 'multiselect', 'attribute_code' => $attributeCode, 'frontend_label' => array(0 => $attributeLabel), 'source_model' => 'evozon_catalog/product_attribute_source_customOptions', 'is_unique' => '0', 'is_required' => '0', 'is_configurable' => '0', 'used_in_product_listing' => '1', 'Default_value' => -1 ); ... //assign attribute to ‘Default’ attribute set and show in the ‘General’ tab $setupModel->addAttributeToSet( Mage_Catalog_Model_Product::ENTITY, 'Default', 'General', $attributeCode );

One important part in your custom module is adding the link to the installer script in the config.xml file:

… … Evozon_Catalog Mage_Catalog_Model_Resource_Setup core_setup ….

Everything I’ve described so far is pretty straight forward. One thing to notice is the ‘source_model’ parameter - you’ll see that we’ve used a custom source model for defining the options.

Based on our install script, we need to define a custom model class. This is how it will look like:

class Evozon_Catalog_Model_Product_Attribute_Source_CustomOptions extends Mage_Eav_Model_Entity_Attribute_Source_Abstract { ... //Options defined for custom attribute public function getAllOptions() { if (!$this->_options) { $this->_options = array( array( 'value' => '-1', 'label' => Mage::helper('catalog')->__('None') ), array( 'value' => '1', 'label' => 'Option 1' ), … ); } return $this->_options; } ... }

Ok, we are ready to check our new attribute in the back-end. This is what you will see when editing a product.

So far so good. Now, assume that you want to further use this attribute as a filter for the product collection or, in our specific case, to hide specific products based on a certain value of our customer attribute. For this we extended Mage_Catalog_Model_Layer and adjusted method prepareProductCollection():

$collection ->addAttributeToSelect( Mage::getSingleton('catalog/config')->getProductAttributes() ) ->addMinimalPrice() ->addFinalPrice() ->addTaxPercents() ->addUrlRewrite($this->getCurrentCategory()->getId()); $collection->addAttributeToFilter( 'special_options', array( array('finset' => $specificValueToFilter), array('null' => true) ), 'left' );

You will not get the expected results, and so the fun begins.

Let’s start by checking the way the collection is built in Mage_Catalog_Model_Resource_Product_Collection: depending on the value you have configured for your catalog, to ‘Use Flat Catalog Product’ or not, Mage::getResourceModel('catalog/product_collection') will either initialize as entity model ‘catalog/product_flat’ or ‘catalog/product’, each with a different resource model and a different database table. In our case, the catalog had ‘Use Flat Catalog Product’ set to ‘Yes’, so the collection will be initialized with ‘catalog/product_flat’.

protected function _construct() { if ($this->isEnabledFlat()) { $this->_init('catalog/product', 'catalog/product_flat'); } else { $this->_init('catalog/product'); } $this->_initTables(); }

Based on this, the database table loaded in our case was catalog_product_flat_store_ . You will find a separate table for each store defined in your shop.

Check the structure of the flat table and you will see all the columns used for listing.

If you don’t follow the code and just want to find out what query is executed for building products collection, you can enable query logging. This can also be very useful if you are not sure where to start with debugging.

Here’s how you can do this.

Open lib/Varien/Db/Adapter/Pdo/Mysql.php and you will notice the following properties:

protected $_debug = false; ... protected $_logAllQueries = false; ... protected $_logCallStack = false;

You need to change the first two properties to true, and you can leave the last one optional, depending on how much information you need. After changing the values, you will have a new log file var/debug/pdo_mysql.log. But, don’t forget, you should never ever edit the core, this is only for debugging purposes.

So, how can we insert a new column in the flat table with our custom attribute?

You need to figure out how these columns were generated and how they are filled in. You can follow the code with the debugger or check an attribute that is already in there and see how the model class looks like.

Let’s say we want to do this the hard way and figure out exactly how Magento works. You need to know that the flat tables are generated when indexes are refreshed. In Mage_Catalog_Model_Resource_Product_Flat_Indexer, the updateEavAttributes() method is called when building/rebuilding the indexes, which then calls the method updateAttribute().

We’re getting closer.

Method updateAttributes() calls getAttributeCodes(). The list of attributes returned by this method will be used for building the flat table columns. So, in order to have our custom attribute show up as a column in the table, it should be present in this list.

$columns = $attribute->getFlatColumns(); if (!$columns) { return $this; } foreach (array_keys($columns) as $columnName) { if (!isset($describe[$columnName])) { return $this; } } $select = $attribute->getFlatUpdateSelect($storeId);

Now let’s look at these two methods:

public function getFlatUpdateSelect($store = null) { if ($store === null) { foreach (Mage::app()->getStores() as $store) { $this->getFlatUpdateSelect($store->getId()); } return $this; } if ($this->getBackendType() == self::TYPE_STATIC) { return null; } //!!!this is us if ($this->usesSource()) { return $this->getSource()->getFlatUpdateSelect($store); } return $this->_getResource()->getFlatUpdateSelect($this, $store); } public function getFlatColumns() { // If source model exists - get definition from it - !!!this is us if ($this->usesSource() && $this->getBackendType() != self::TYPE_STATIC) { //please make note of the type in the methods name return $this->getSource()->getFlatColums(); } if (Mage::helper('core')->useDbCompatibleMode()) { return $this->_getFlatColumnsOldDefinition(); } else { return $this->_getFlatColumnsDdlDefinition(); } }

Please notice the method’s name: ‘getFlatColums()’ (not ‘getFlatColumns()’). You just have to love this about Magento, right?

Almost there. If you go one step further you will see how these methods are implemented. In Mage_Eav_Model_Entity_Attribute_Source_Abstract:

public function getFlatColums() { return array(); } public function getFlatUpdateSelect($store) { return null; }

So, no column defined for our custom attribute and no query to build the data. This means that all we need to do is extend these methods in our custom model attribute class. Cool, right?

I’m gonna admit, at this point I cheated and looked at an existing attribute with the two methods already implemented. I only changed the code so that we have the right parameters based on the data type our attribute is using. But, I think the code makes sense and it’s not too complicated to follow:

/** * Get columns for flat table * * @return array */ public function getFlatColums() { $attributeCode = $this->getAttribute()->getAttributeCode(); $attributeDefaultValue = $this->getAttribute()->getDefaultValue(); $attributeType = $this->getAttribute()->getBackendType(); $isNullable = is_null($attributeDefaultValue) || empty($attributeDefaultValue); $column = array( 'unsigned' => false, 'default' => $isNullable ? null : $attributeDefaultValue, 'extra' => null ); if (Mage::helper('core')->useDbCompatibleMode()) { $column['type'] = $attributeType; $column['is_null'] = true; } else { $column['type'] = Mage::getResourceHelper('eav')->getDdlTypeByColumnType($attributeType); $column['nullable'] = true; $column['comment'] = $attributeCode . ' column'; } return array($attributeCode => $column); } /** * Get select for flat update select * * @param int $store * @return array */ public function getFlatUpdateSelect($store) { return Mage::getResourceSingleton('eav/entity_attribute') ->getFlatUpdateSelect($this->getAttribute(), $store); }

In the example above, we defined a default value for the column (your installer script should also define the default value), and you can get this from your custom attribute.

If you now trigger a reindex for all the products, or just save a product, you will notice the new column with the actual product attribute value in the flat product table. If your attribute also needs an index, you need to implement method getFlatIndexes(). You can check Mage_Catalog_Model_Product_Status as an example of implementing this method.

Conclusion

To wrap it up, when creating an attribute with custom source model you need to extend Mage_Eav_Model_Entity_Attribute_Source_Abstract. You need to implement the method getAllOptions() and, if you have flat catalog product enabled, getFlatColums() and getFlatUpdateSelect().

Maybe if you had looked straight at Mage_Catalog_Model_Product_Visibility, you would have found the answer faster. Maybe not. But the important thing is that you’ve figured out how Magento works.