SQL: Disable empty categories

[TRESSET]

Good afternoon. I would like to disable the empty categories via SQL. Without products, or without active products in category or subcategories.

 

I have this code to disable products without stock, and I try to adapt it to the categories, but I do not know how to make the query:

 

UPDATE ps_product_shop SET active=0 WHERE id_shop=X and id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0 and id_shop=X GROUP BY id_product);

 

Does anyone know how I can do the query?

 

How can I check the number of active products per category?

 

Multistore PS 1.7.1.1

 

Thank you very much! 

Edited June 1, 2017 by TRESSET (see edit history)

[musicmaster]

 

Good afternoon. I would like to disable the empty categories via SQL. Without products, or without active products in category or subcategories.

 

 

First of all I would simplify the requirement to: "Without active products or subcategories". That will mean that you have to run the query several times for changes in subcategories.

 

The query will look something like this (this is from the top of my head and very likely contains some errors).  I assume that you know enough to fix it:

UPDATE ps_category c SET active=0 WHERE NOT EXISTS (SELECT NULL FROM ps_category_product cp LEFT JOIN ps_product_shop ps ON cp.id_product=ps.id_product WHERE active=1 AND c.id_category=cp.id_category) AND NOT EXISTS (SELECT NULL FROM ps_category c2 WHERE c2.active=1 AND c2.id_parent=c.id_category) 

[knekke]

Hi,

I would like to run that query, but my sql skills are quite limited. I can run it successfully on a test Database that runs MariaDB, but on the production server I get an error:

SQL Error [1093] [HY000]: You can't specify target table 'c' for update in FROM clause

After some googling I get what the problem is, but unfortunately I am not able to modify the query to make it work :/

[musicmaster]

It would help if you told us what you tried. What I see recommended is putting another query right in front of this one.

[knekke]

UPDATE ps_category SET active=0 
WHERE id_category IN
(SELECT id_category
FROM ps_category c
WHERE NOT EXISTS 
	(SELECT NULL FROM ps_category_product cp LEFT JOIN ps_product_shop ps ON cp.id_product=ps.id_product WHERE active=1 AND c.id_category=cp.id_category) 
AND NOT EXISTS 
	(SELECT NULL FROM ps_category c2 WHERE c2.active=1 AND c2.id_parent=c.id_category));

This was my latest try...

[musicmaster]

You said you had done an internet search. If I do that the first result is

https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

And that says that right in front of the query you should run

SET optimizer_switch = 'derived_merge=off';

Did you do that?

[knekke]

Thanks! I saw that post and tried that line. But unless I am doing something wrong it doesn't help and the error stays the same.

Other than that this seems to be a dirty workaround, and while I have to deal with sql I would like to learn the proper way.

 

[knekke]

I figured it out:

UPDATE ps_category SET active=0 
WHERE id_category IN
(SELECT * FROM
(SELECT id_category
FROM ps_category c
WHERE NOT EXISTS 
	(SELECT NULL FROM ps_category_product cp LEFT JOIN ps_product_shop ps ON cp.id_product=ps.id_product WHERE active=1 AND c.id_category=cp.id_category) 
AND NOT EXISTS 
	(SELECT NULL FROM ps_category c2 WHERE c2.active=1 AND c2.id_parent=c.id_category)) AS x);