I've added an attribute:
$installer->addAttribute(Mage_Catalog_Model_Product::ENTITY, 'show_in_subview', array( 'type' => 'varchar', 'label' => 'Show in Subview', 'input' => 'multiselect', 'backend' => 'eav/entity_attribute_backend_array', 'required' => 0, 'user_defined' => 0, 'visible' => 1, 'is_filterable' => 1, 'is_configurable' => 0, 'sort_order' => 14, 'source' => 'ho_subviews/attribute_source_subview', 'global' => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL, 'group' => 'General', 'used_in_product_listing' => true, ));
As you can see I've used a custom source to fill the multi select field. Saving and loading the values works like a charm.
Filtering the collection can be done using: $collection->addAttributeToFilter('show_in_subview', array('finset'=> Mage::app()->getStore()->getCurrentSubviewId()));
The problem is that the collection doesn't use indexes since the column type isn't SET (http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set).
I noticed that when adding a normal multi-select field, the query is a join on the catalog_product_index_eav table which does use indexes and thus will be more performant.
I'm not exactly sure where to look to use this table as well. I'm guessing the backend class is responsible for that (that was the only difference between a manually created drop down field or the above). So eav/entity_attribute_backend_array doesn't use the catalog_product_index_eav table.
- What is the default backend that is used for multiselect or isn't there any?
- How can I extend eav/entity_attribute_backend_array so it uses the catalog_product_index_eav table?