HaCos Posted October 6, 2022 Share Posted October 6, 2022 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 Link to comment Share on other sites More sharing options...
ps8modules Posted October 7, 2022 Share Posted October 7, 2022 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. Link to comment Share on other sites More sharing options...
HaCos Posted October 7, 2022 Author Share Posted October 7, 2022 The query becomes more complex than i expected. Any ideas how to get possible "empty" categories aka with no products for sale? Link to comment Share on other sites More sharing options...
ps8modules Posted October 7, 2022 Share Posted October 7, 2022 (edited) 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) Link to comment Share on other sites More sharing options...
HaCos Posted October 7, 2022 Author Share Posted October 7, 2022 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.. Link to comment Share on other sites More sharing options...
ps8modules Posted October 7, 2022 Share Posted October 7, 2022 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now