DEV Community

Khaled Said
Khaled Said

Posted on

Beyond Unique Constraints in Odoo

Inspired by

PostgreSQL exclusion constraints: Beyond UNIQUE ~ by Hans-Jürgen Schönig

In Odoo platform you could apply model constraints in two methods. the first one is using SQL constraints and the second is using Python. Odoo provide Python constraints to give you the capability to apply checks on sophisticated logic.

An example of applying unique using SQL constraints in Odoo framework by adding _sql_constraints class attribute.

_sql_constraints = [
    (
        'code_company_uniq',
        'unique (code,company_id)',
        'The code of the account must be unique per company !'
    )
]
Enter fullscreen mode Exit fullscreen mode

code snippet example

such simple constrains could be applied using python logic as following:

@api.constrains("code", "company_id")
def _uniqe_code_per_company(self):
    records = self._read_group(
        domain=[("code", "=", self.code), ("company_id", "=", self.company_id.id)],
        fields=["company_id", "ids:array_agg(id)"],
        groupby=["company_id"],
    )
    for rec in records:
        if rec["company_id_count"] >= 2:
            raise ValidationError(_("The code of the account must be unique per company !"))
Enter fullscreen mode Exit fullscreen mode

code snippet example

as it appears the Python code is more than the SQL code. in a different scenario you may need to check uniqueness between ranges data-structures. like dates cannot be overlapped, applying such scenario using Python would require a lot of logic. luckily, we could use Postgresql exclusion constraints in such case. imagine you have the following model.

KPI NAME Bottom Percentage Top Percentage
Sales Target Achieved 85% 95%
Sales Target Achieved 95% 105%
Sales Target Achieved 105% 125%
Sales Target Achieved 125% 150%

and the model definition in Odoo is as following:

class SalesKPIPercentage(models.Model):
    _name = "sales.kpi.percentage"

    type_kpi_id = fields.Many2one("sales.kpi.type", string="KPI Name", required=True)
    percent_bottom = fields.Float(
        string="Bottom Percentage",
        required=True,
    )
    percent_top = fields.Float(
        string="Top Percentage",
        required=True,
    )
Enter fullscreen mode Exit fullscreen mode

to apply a constraints so no records to have overlapped percentage with the same type. you could use SQL contraints usign GIST as following:

_sql_constraints = [
    (
        "no_overlapped_kpis",
        """
            EXCLUDE USING GIST (
                numrange(percent_bottom::numeric, percent_top::numeric) WITH &&,
                int4range(type_kpi_id, type_kpi_id, '[]') WITH =
            )
        """,
        "KPI category Overlapped",
    ),
]
Enter fullscreen mode Exit fullscreen mode

please note that you could also compare against fields.Char definition. forexample if the field definition of type_kpi_id is of type fields.Selection as:

type_kpi_id = fields.Many2one([
    ('sale_target_amount', 'Sales Target Amount'),
    ('sale_target_qty', 'Sales Target Quantity'),
], string="KPI Name", required=True)
Enter fullscreen mode Exit fullscreen mode

the SQL constraints would be as:

_sql_constraints = [
    (
        "no_overlapped_kpis",
        """
            EXCLUDE USING GIST (
                numrange(percent_bottom::numeric, percent_top::numeric) WITH &&,
                type_kpi_id WITH =
            )
        """,
        "KPI category Overlapped",
    ),
]
Enter fullscreen mode Exit fullscreen mode

and in such case you will need to enable a postgresql btree_gist extension using a pre_init_hook as:

def add_pg_extensions(cr, registry):
    cr.execute("CREATE EXTENSION IF NOT EXISTS btree_gist")
Enter fullscreen mode Exit fullscreen mode

Top comments (0)