I just stumbled over an interesting problem. A customer of mine told me, that they are trying for weeks to get filters working and for some reason it just didn’t work. It sounded like an easy to fix problem to me, but the fact, that the (good) developers of the customer didn’t fix it themselves scared me a little. But I love to take care of hard to crack nuts.
The filter was not shown at all. Once change in the code to show all filters (nonetheless, wether they have products attached in “this category” or not) I saw the filter, but there were not items/values in it.
Why are there no items?
For the filters Magento counts the products in the category which match, this happens in:
-- my_product_type is the attribute I want to filter by with the id 191 SELECT my_product_type_idx.value, COUNT(my_product_type_idx.entity_id) AS count FROM catalog_product_entity AS e INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id = e.entity_id AND cat_index.store_id = 1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '2644' INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 INNER JOIN catalog_product_index_eav AS my_product_type_idx ON my_product_type_idx.entity_id = e.entity_id AND my_product_type_idx.attribute_id = '191' AND my_product_type_idx.store_id = '1' GROUP BY my_product_type_idx.value
This query returns nothing.
Ok, why? Because
catalog_category_product_index doesn’t contain any rows for the attribute 191.
Just to be sure I rebuild the index, but as expected, that changed nothing.
Ok, what attributes are indexed with
catalog_product_attribute? This question is answered in the indexer:
If an attribute is multiselect, it is only indexed if the backend type is
Solution is to change the backend-type to text and copy over all values from the varchar table to the text table.
<?php /** @var Mage_Catalog_Model_Resource_Setup $installer */ $installer = $this; $installer->startSetup(); $attributeCode = 'my_product_type'; $installer->updateAttribute(Mage_Catalog_Model_Product::ENTITY, $attributeCode, 'backend_type', 'text'); $attributeId = $installer->getAttributeId(Mage_Catalog_Model_Product::ENTITY, $attributeCode); $query = "INSERT INTO catalog_product_entity_text SELECT null, entity_type_id, attribute_id, store_id, entity_id, value FROM catalog_product_entity_varchar WHERE attribute_id = $attributeId"; $installer->getConnection()->query($query); $installer->endSetup();
We can argue wether shooting queries directly on a Magento database is a good idea, but imho queries which only run once are a good reason to not invest too much into “how does this insert into exactly work the magento way?” 🙂
In Magento I tend to create
multiselect attributes as
varchar, that does not work, they need to be