6

I am using the fastsimpleimport module to create my custom import. This has a setDropdownAttributes() function to automatically create needed attribute values but it does not remove unused values. Those still show up in my advanced search.

How can I search and remove unused attribute values?

I think they are stored in eav_attribute_option_value but I am not sure how to check if they are used by a product.

0
delete c,b
from eav_attribute a
inner join eav_attribute_option b on a.attribute_id = b.attribute_id
inner join eav_attribute_option_value c on c.`option_id` = b.option_id
LEFT join catalog_product_entity_int pi on b.option_id=pi.value and pi.store_id=0 and b.attribute_id=pi.attribute_id
where entity_id is null

Try this Query..

  • 1
    if you copy and paste the solution from somewhere else show at least some respect by posting a link back to the source. – user487772 Mar 4 '14 at 10:21
  • Also this query will kill all values of multiple select attributes as they are stored in catalog_product_entity_varchar and not catalog_product_entity_int – user487772 Mar 5 '14 at 12:20
14

LuFFy's answer is technically incorrect as it has 3 issues:

  • it will remove values for admin store view only leaving some store-views related garbage
  • it will remove unused values of attributes of all entity types
  • it will remove ALL values from attributes with "multiple select" type regardless if they are assigned to any products or not because they options are stored in catalog_product_entity_varchar and not catalog_product_entity_int

The query below addresses these problems:

    DELETE o, v
      FROM `eav_attribute` a
INNER JOIN `eav_attribute_option` o ON a.`attribute_id` = o.`attribute_id`
INNER JOIN `eav_attribute_option_value` v ON v.`option_id` = o.`option_id`
INNER JOIN `eav_entity_type` t ON t.`entity_type_id` = a.`entity_type_id`
 LEFT JOIN `catalog_product_entity_int` pi ON o.`option_id` = pi.`value` AND o.`attribute_id` = pi.`attribute_id`
 LEFT JOIN `catalog_product_entity_varchar` pv ON o.`option_id` = pv.`value` AND o.`attribute_id` = pv.`attribute_id`
     WHERE pi.`entity_id` IS NULL
       AND pv.`entity_id` IS NULL
       AND t.`entity_type_code` = "catalog_product"
  • @tim-bezhashvyly This is a much better answer. However, this doesn't correctly match multiple-select attributes. If you change ON o.option_id = pv.value to ON FIND_IN_SET(o.option_id, pv.value) then this will correctly match multiple-select attributes. – Tyler V. Mar 28 '15 at 2:21
  • Might I also suggest that you add a.is_user_defined = 1 to your WHERE clause as well. You likely don't want to remove any attribute values from system attributes. – Tyler V. Mar 28 '15 at 3:13
  • This doesn't work on multi-select – TheWebsiteGuy Jan 24 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.