I have a lot of disabled categories that are having a lot of products. Whenever I search for something a lot of those products still show up in the search.

Now I know that I can go manually to each product and set the Visibility to "Not Visible Individually" but this is a very tedious job. I first have to see what products that disabled category has. Then I have to copy it's SKU, then go to the products search, paste that SKU, edit the product, change the visibility, click save... and repeat.

I have at least 500 products in those disabled categories.

Is it possible to automatically look for the disabled categories and NOT show up the products that those categories have?

Thank You

up vote 2 down vote accepted

You can extend the search functionality to do this task but that won't be recommended. You can select those products from admin panel change their visibility to Catalog only.

If you really want to this and for one time only then write a sql query and execute that.

UPDATE catalog_product_entity_int as eav 
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
  SET eav.value='2'
where ea.attribute_code = 'status' and eav.entity_id IN 
 (select product_id from catalog_category_product
where category_id IN  
(SELECT distinct(e.entity_id)
FROM catalog_category_entity e
JOIN catalog_category_entity_int eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
  where attribute_code='is_active' and value=0));

Query Description

This query is selecting categories which all have status inactive

SELECT distinct(e.entity_id)
FROM catalog_category_entity e
JOIN catalog_category_entity_int eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
  where attribute_code='is_active' and value=0;

Then select products from catalog_category_product table which all are associated with these categories.

SELECT product_id FROM catalog_category_product
 WHERE category_id IN  
(SELECT distinct(e.entity_id)
FROM catalog_category_entity e
JOIN catalog_category_entity_int eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
  WHERE attribute_code='is_active' AND value=0)

Then Set value for attribute which you want to change. If you want to disable the product then use status attribute or just to change the visibility you can select visibility attribute and add respective value.

UPDATE catalog_product_entity_int as eav 
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
  SET eav.value='0'
where ea.attribute_code = 'status' and eav.entity_id 
  • Problem is that I can't select all those products at once. I need to find all the disabled categories, then I need to find all the products of those disabled categories, and only then I can set them to Catalog only. Any idea how to do this? Thank You – PET Jun 23 '16 at 19:03
  • You can do this from mysql but the point is, if the product is associated with another category which is active ? Are you sure with the scenario then you can have some joins in your sql and you will be able to get the id's which need to be updated. – Aman Srivastava Jun 23 '16 at 19:05
  • I don't think any of the products are part of multiple categories. Anyone have any idea on how to do the SQL line? Thank You – PET Jun 23 '16 at 19:10
  • You can take reference from this gist.github.com/ticean/735798 You can customize this or I'll write for you later. Its midnight here. m out for sleep now. – Aman Srivastava Jun 23 '16 at 19:40
  • I havn't SQL in something like 7 years. I would love if it would be possible for you to help me. I'm sure this would be useful for some other people. – PET Jun 23 '16 at 19:49

I would write an sql query selecting the products in the category ( catalog_category_products), match their id by a select from the catalog_product_entity table and setting their visibility to 0.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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