Magento 1: Multiselect filter don’t show up/have no items

Posted byFabian BlechschmidtPosted inAllgemein, TechStuffTags:,

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 problem

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:

\Mage_Catalog_Model_Resource_Layer_Filter_Attribute::getCount()
-- 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.

Digging deeper

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:

\Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source::_getIndexableAttributes

If an attribute is multiselect, it is only indexed if the backend type is text.

Solution

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?” 🙂

TL;DR

In Magento I tend to create multiselect attributes as varchar, that does not work, they need to be text.

Leave a Reply