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?
Mage_Eav_Model_Entity_Attribute_Backend_Array
is imploding an array into CSV inbeforeSave()
. That would explain why it's probably resulting in aWHERE field IN (a,b,c)
in the query somewhere which isn't going to be very performant. If you know of another EAV attribute that is using indexes nicely, maybe you can look at the backend that it's using...?(...) WHERE FIELD_IN_SET('5')
which can search through a csv field, but doesn't use indexes it uses a table scan.IN()
andFIELD_IN_SET()
. I would imagine that neither will use indexes...?a
orb
, in that case u useIN()
. In this case the field is a multiselect field and the values become:a,b
andb,c
. Now if you want all the rows that containb
you can useFIELD_IN_SET()
Mage_Eav_Model_Entity_Attribute_Source_Table
as a reference, noting thegetFlatColums
,getFlatColums
,getFlatIndexes
,getFlatUpdateSelect
methods.