Constraints with better performence

Question

In my model, client_id must be one per product. So I want to make a constraint for this situation.

class ClientSystemCode(models.Model):
    _name = 'client.system.code'
    _description = 'Client System Code'

    client_id = fields.Many2one('res.partner', 'Client')
    product_id = fields.Many2one('product.template', 'Product')
    client_sys_code = fields.Char('Client system code')

in product.template model my constraints look like this.

@api.constrains('client_system_code_ids')
    def _client_system_code_constraint(self):
        duplicates = []
        for line in self.client_system_code_ids:
            if line.client_id.id not in duplicates:
                duplicates.append(line.client_id.id)
            else:
                raise ValidationError(_("Product can't have more than one client code with same client"))

it is ok as it triggered from product form view and there always be not that much lines. But constraint in client.system.code should be better performance wise because there can be thousands of lines. So is there any kind of better solutions?

Answer 1

You will get it done easily using an sql constraint like:

class ClientSystemCode(models.Model):
    _name = 'client.system.code'

    _sql_constraints = [
        ('client_product_unique', 'unique (client_id, product_id)', 'Product can't have more than one client code with same client'),
    ]