M2M2: Attributes not showing
Published on 07 Aug 2019If 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.