Disabled categories still show products in search

Question

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

Answer 1

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 

Answer 2

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.