Query: Products (count) by category

[HaCos]

Hello,

We manage a store with more than lots of categories & many more SKUs.
Because a big part of our catalog is getting updated in bulk, i am looking for a way to check if any our categories is left with 0 products available for order. 
I am thinking of creating a query that will return:  
Category name, total products, enabled products, products available for order

Any ideas, more than welcome

[ps8modules.com]

hi
You can use filtering in the product list. In the list of products, you choose quantity = 0 and you can filter. The name of the default category is also in the list. You can also export the result or export it to the SQL query manager.

[HaCos]

The query becomes more complex than i expected. Any ideas how to get possible "empty" categories aka with no products for sale? 

[ps8modules.com]

The SQL query is not complicated, you just wanted something different in your first post.
If you want to get the id of the category and the name of the category in which there is no product, the sql is, for example, like this:

SELECT t1.name, t1.id_category
FROM ps_category_lang t1
LEFT JOIN ps_category_product t2 ON t2.id_category = t1.id_category
WHERE t2.id_category IS NULL AND t1.id_lang = 1 AND t1.id_category > 1
GROUP BY t1.id_category

 

Edited October 7, 2022 by 4you.software (see edit history)

[HaCos]

27 minutes ago, 4you.software said:

The SQL query is not complicated, you just wanted something different in your first post.
If you want to get the id of the category and the name of the category in which there is no product, the sql is, for example, like this:

SELECT t1.name, t1.id_category
FROM ps_category_lang t1
LEFT JOIN ps_category_product t2 ON t2.id_category = t1.id_category
WHERE t2.id_category IS NULL AND t1.id_lang = 1 AND t1.id_category > 1
GROUP BY t1.id_category

 

Thank you but i need to check if there are any categories left with 0 products available for order - which requires at least to include products & products combination table, for which we will need to check if there visibility if both and not something else, available for order is checked,  deny when out of stock is unchecked.. 
 

[ps8modules.com]

Probably like this.
The best and fastest solution than sql query is to use Prestashop functions.
Look in Product.php how complex sql is composed of functions for displaying products and you still need to add another filter to that.
Unfortunately I can't be of more help.