M2M2: Attributes not showing

Published on 07 Aug 2019

If attributes don’t show up in the layered navigation and it isn’t due to the usual suspects, it’s probably a multiselect attribute. It probably got migrated as type text where it should be varchar. An easy SQL fix is this;

See if this issue affects the website (you will get no records returned if you are okay):

SELECT * from eav_attribute WHERE entity_type_id=4 AND frontend_input="multiselect";

Copy data values from text to varchar table:

INSERT INTO catalog_product_entity_varchar (store_id, attribute_id, entity_id, value) SELECT store_id, attribute_id, entity_id, value FROM catalog_product_entity_text WHERE catalog_product_entity_text.attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE frontend_input="multiselect" AND backend_type="text") AND catalog_product_entity_text.value is not null;

Delete text values:

DELETE FROM catalog_product_entity_text WHERE attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE frontend_input="multiselect" AND backend_type="text");

Change attribute type:

UPDATE eav_attribute SET backend_type="varchar" WHERE frontend_input="multiselect" AND backend_type="text" AND entity_type_id=4;

After this reindex the website, clear the cache, and the attributes should appear in the layered navigation.

Development

Everything web development related, rants, snippets, tutorials, quests; it’s all there.

All categories

All tags

All posts