[SOLVED] incorrect category id persists in database

[JuanTomas]

SOLVED: if you want to use SQL queries to assign products to categories, you have to update the category id in three (3) places:

 

ps_product.id_category_default (shokinro apparently didn't read my original question; I had already noticed this)

ps_category_product.id_category

ps_product_shop.id_category_default

 

ps_product_shop duplicates several fields from ps_product, including not only id_category_default but also price.  Where there is a discrepancy between ps_product and ps_product_shop, ps_product_shop seems to always win.  This can drive you crazy when you update the values in ps_product and nothing happens.

 

Hope this helps someone!

 

-----original question below-------

 

Using Prestashop 1.6.1.1

 

I had a category (call it category A) with many Imperial products and many Metric products.  I decided to create separate subcategories for Imperial and Metric and move all the products into those new categories.  Once the new categories were created, I used a couple of database queries to update ps_product.id_category_default and ps_category_product.id_category for the selected products.

 

Now, in the front view, all the products appear in the new categories they belong to.  In the back view, when looking at products and filtering by category, the products also appear in the correct places, but the Category column still shows category A!

 

So, when I filter products by category, and go into "Metric" category, I see all the products that belong there, but the category column shows all category A, not category Metric.  The same products are not seen at all when viewing products in category A.

 

If I edit one of these products, the category association shows correctly (e.g. Metric and not A).  If I change nothing and save, then the category information magically becomes correct in the BO product listing, and the product is "fixed".  This matters, because I have a ProductController override which uses id_category_default to make some display decisions:

 

$category = new Category($this->product->id_category_default);

// Use properties of $category to do stuff

// Wait, why'd it return category A???  This product is in Metric!

 

This override starts to work normally on products where I've "re-saved".  Studying the database, I don't see any difference between "re-saved" products and the others.  ps_product.id_category_default and ps_category_product.id_category don't get changed by the "re-save".  But somehow re-saving the product gets rid of the weird behavior.

 

I *could* just edit and re-save all the products, but there are hundreds, and the whole idea of using SQL queries was to avoid the manual process.

 

I tried clearing the cache under advanced-> performance.  That didn't fix the problem.

 

Where, other than ps_product.id_category_default and ps_category_product.id_category, might this incorrect mapping persist in the database?

Edited November 16, 2015 by JuanTomas (see edit history)

[shokinro]

In ps_product page, there is a field called  id_category_default which keeps the default category of the product.

 

You use SQL to update product category relation data in database, but you did not update the id_category_default, so it is still pointing to old category.

 

When you open product to edit, the Default category will be automatically set to the first category of the categories the the products belongs to[in the drop down list for Default Category] , so when you save it, it automatically fix the issue for that product.

 

You can manually open each product, go to "Association" tab and then save it.  Or you can write SQL to update id_category_default field in table ps_product too.

[France Adn]

Hello, i have the same problem, default categories aren't right after a migration from 1.4 to 1.6

My default categories are right in Product page information (in Back office) but ids categories are false in my Database ( Homepage default)

 

Do you have any script that i could use to trigger function to update every default categories for every product ? I have like 73XX product, i can't do this manually. 

 

Thanks