whiteletters in blankpapers whiteletters in blankpapers - 6 months ago 135
SQL Question

Mass product category update (adding `home` category to all products) - Prestashop 1.6.0.6

I observed that the count of all products in my website is not equal to the number of products in category

home
. It is due to the fact that this biggest parent category was not always selected during product adding. So some products belong to
home
but others not.

While I need to display the correct number of products for the category
home
page, I was looking in the database for the columns that holds the categories but not found them in
ps_product
or
ps_product_lang
.

what is the idea behind an sql query that can mass modify the appropriate fields in database so that all my products belong to category
home
.

Your usual help is appreciated. Thank you.

Edit:
I found that the table in database is
ps_category_product
. Is it the only one?

this table is like this:

+-------------+------------+----------+
| id_category | id_product | position |
+-------------+------------+----------+
| 11 | 1 | 1 |
| 11 | 2 | 2 |
| 11 | 10 | 3 |
+-------------+------------+----------+


I need maybe to create x lines (x= the number of products) . each line has id_category=1 and id_product= product id and position= position++. Is is the best approach?

Any help regarding how to implement it in sql?

Thanks again.

Answer

You can assign every product to the Home category with a simple SQL query:

INSERT IGNORE INTO ps_category_product SELECT 2, id_product, 0 FROM ps_product

2 is normally the id_category value of the Home category, 0 is the position.

If you want unique position values then you need a more complex query:

INSERT IGNORE INTO ps_category_product SELECT 2, id_product, ((SELECT MAX(position) FROM ps_category_product WHERE id_category = 2) + (@inc := @inc + 1)) FROM ps_product INNER JOIN (SELECT @inc := 0) AS i
Comments