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.
(
‘code_company_uniq‘,
‘unique (code,company_id)‘,
‘The code of the account must be unique per company !‘
)
]
such simple constrains could be applied using python logic as following:
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 !“))
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:
_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,
)
to apply a constraints so no records to have overlapped percentage with the same type. you could use SQL contraints usign GIST as following:
(
“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“,
),
]
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:
(‘sale_target_amount‘, ‘Sales Target Amount‘),
(‘sale_target_qty‘, ‘Sales Target Quantity‘),
], string=“KPI Name“, required=True)
the SQL constraints would be as:
(
“no_overlapped_kpis“,
“””
EXCLUDE USING GIST (
numrange(percent_bottom::numeric, percent_top::numeric) WITH &&,
type_kpi_id WITH =
)
“””,
“KPI category Overlapped“,
),
]
and in such case you will need to enable a postgresql btree_gist extension using a pre_init_hook as:
cr.execute(“CREATE EXTENSION IF NOT EXISTS btree_gist“)