[14.0]search method weird behaviour when called from mapper

[flotho]

I'll try to explain it as clear as possible because this one is really weird.

I'm running this connector https://github.com/OCA/connector-prestashop/pull/155 successfully. My issue is concerning this part of the code https://github.com/OCA/connector-prestashop/blob/14.0/connector_prestashop/models/product_product/importer.py#L326 This part of the code is first looking into the database if any product already has the same default_code and if yes it returns the id of the product.

This part is working correctly for many products but in some cases it returns nothing even if a product with the default code exist in the db (checked by sql).

To be sure I changed locally slightly a bit the code into something like this :

@only_create
@mapping
def odoo_id(self, record):
        """Will bind the product to an existing one with the same code"""
        if self.backend_record.matching_product_template:
            code = record.get(self.backend_record.matching_product_ch)
            if self.backend_record.matching_product_ch == "reference":
                if code:
                    _logger.error("BEFORE searching for %s" % code)
                    product = self.env["product.product"].search(
                        [("default_code", "=", code)]
                    )
                    _logger.error("juste after searching for %s with result %s and before SQL" % (code, product))
                    self.env.cr.execute(
                    """
                    SELECT id FROM product_product
                    WHERE default_code=%s""",
                    (code, ),
                    )
                    result = self.env.cr.fetchone()[0] or False
                    _logger.error("juste after SQL for %s with result %s" % (code, result))
                    
                    if product:
                        return {"odoo_id": product.id}

.../...

and i produce this kind of logs :

2022-07-23 23:39:35,217 503244 ERROR 14-wip-test odoo.addons.connector_prestashop.models.product_product.importer: BEFORE searching for T21HDC00107T140 
2022-07-23 23:39:35,217 503253 INFO 14-wip-test werkzeug: 127.0.0.1 - - [23/Jul/2022 23:39:35] "POST /web/dataset/call_kw/queue.job/read HTTP/1.1" 200 - 15 0.007 0.019
2022-07-23 23:39:35,217 503244 DEBUG 14-wip-test odoo.sql_db: query: SELECT "product_product".id FROM "product_product" LEFT JOIN "product_template" AS "product_product__product_tmpl_id" ON ("product_product"."product_tmpl_id" = "product_product__product_tmpl_id"."id") LEFT JOIN "ir_translation" AS "product_product__product_tmpl_id__name" ON ("product_product__product_tmpl_id"."id" = "product_product__product_tmpl_id__name"."res_id" AND "product_product__product_tmpl_id__name"."type" = 'model' AND "product_product__product_tmpl_id__name"."name" = 'product.template,name' AND "product_product__product_tmpl_id__name"."lang" = 'en_US' AND "product_product__product_tmpl_id__name"."value" != '') WHERE (("product_product"."active" = true) AND ("product_product"."default_code" = 'T21HDC00107T140')) AND (("product_product__product_tmpl_id"."company_id" in (1)) OR "product_product__product_tmpl_id"."company_id" IS NULL ) ORDER BY  "product_product"."default_code" ,COALESCE("product_product__product_tmpl_id__name"."value", "product_product__product_tmpl_id"."name") ,"product_product"."id"   
2022-07-23 23:39:35,218 503244 ERROR 14-wip-test odoo.addons.connector_prestashop.models.product_product.importer: juste after searching for T21HDC00107T140 with result product.product() and before SQL 
2022-07-23 23:39:35,218 503244 DEBUG 14-wip-test odoo.sql_db: query: 
                    SELECT id FROM product_product
                    WHERE default_code='T21HDC00107T140' 
2022-07-23 23:39:35,219 503244 ERROR 14-wip-test odoo.addons.connector_prestashop.models.product_product.importer: juste after SQL for T21HDC00107T140 with result 1115

As you can see, running the query return the id 1115 in the last ERROR message.

Additionnaly I tried to execute the logged query SELECT "product_product".id FROM "product_product" LEFT JOIN "product_template" AS "product_product__product_tmpl_id" ON ("product_product"."product_tmpl_id" = "product_product__product_tmpl_id"."id") LEFT JOIN "ir_translation" AS "product_product__product_tmpl_id__name" ON ("product_product__product_tmpl_id"."id" = "product_product__product_tmpl_id__name"."res_id" AND "product_product__product_tmpl_id__name"."type" = 'model' AND "product_product__product_tmpl_id__name"."name" = 'product.template,name' AND "product_product__product_tmpl_id__name"."lang" = 'en_US' AND "product_product__product_tmpl_id__name"."value" != '') WHERE (("product_product"."active" = true) AND ("product_product"."default_code" = 'T21HDC00107T140')) AND (("product_product__product_tmpl_id"."company_id" in (1)) OR "product_product__product_tmpl_id"."company_id" IS NULL ) ORDER BY "product_product"."default_code" ,COALESCE("product_product__product_tmpl_id__name"."value", "product_product__product_tmpl_id"."name") ,"product_product"."id" in pgadmin to be sure of the query produced by the ORM and I got something :

Then I reuse the same query directly in the code and it produce no result. I change the query to use this one in the code :

SELECT "product_product".id FROM "product_product" 
LEFT JOIN "product_template" AS "product_product__product_tmpl_id" ON ("product_product"."product_tmpl_id" = "product_product__product_tmpl_id"."id") 
WHERE (("product_product"."active" = true) AND ("product_product"."default_code" = 'T21HDC00107T140')) 
AND (("product_product__product_tmpl_id"."company_id" in (1)) OR "product_product__product_tmpl_id"."company_id" IS NULL ) 
ORDER BY  "product_product"."default_code" 

nothing better

[flotho]

For a reason that I don't understand, the products are updated as inactive during the transaction and if I force the active_test=False in the search query, it works https://github.com/OCA/connector-prestashop/pull/155/files#diff-221236eb7850ab74a7096b4bec1b724aee0fe7647f766d1d0ea1add6a2d409dbR328

Now I have to understand how a product that :

• is active
• is bound to a template that is also active
• updated by a PS product that is also active could be inactive during the transaction

[flotho]

Hi @guewen any clue on what could change the active state of an object during a mapper transaction ?

[flotho]

@hailangvn , any idea on this one ?

[guewen]

Maybe a write on the template attributes that regenerates the variants? I'm not sure if it's done in every Odoo versions, but I remember that when changing attributes values, it used to disable all variants and generate new ones.

[flotho]

ah...... very interesting ! I'll check this one.

[simahawk]

yeah, odoo will mess up your variants if you don't block it w/ create_product_product in ctx key

FTR to search for specific variants in a template it is preferable to use the combination_indices as odoo does.

[hailangvn]

Good day, I am late to the party. :)

Besides above checking suggestion, I found three places that can set active to False during creation of products.

• connector_prestashop/models/product_template/importer.py > deactivate_default_product
• connector_prestashop/models/product_product/common.py > unlink
• odoo/14.0/addons/product/models/product_template.py > _create_variant_ids > variants_to_unlink._unlink_or_archive()

IMHO, the hypothesis is that, the product with the specific code is being used by any sale order or other documents. During importing, for unknown reason yet, execution flow had gone to one of above three places so the product was deactivated.

May need more time to re-produce the issue.