In odoo, domain is a list that contains certain expressions that are used to filter some data from the database. Domain will be translated by odoo into an SQL expression to fill the where clause. For example, if we have a domain like this.

[('state','=','draft')]

The domain above if applied to the sale.order model will produce an SQL expression like this.

WHERE (
	("sale_order"."state" = 'draft')
)

Domain can be used in python to find some data in a model, or in relational field to limit the dropdown options. It also can be used in xml file, for example in a search view to limit the data that appears in the list / tree view.

Domain also can be used to set the attribute of a field, for example to set the readonly attribute, which is to set the field so that it can be edited or cannot be edited under certain conditions.

As an odoo programmer understanding domain is vital. Unfortunately this domain is written in Polish Notation. Where the operator is written before the operand. For example, if we have the 1 + 2 expression, if it is written in Polish Notation it will be + 1 2. For someone who don’t know the Polish Notation before, this is very confusing, this is what I experienced when I started working as an odoo programmer.

Therefore, we must have some tricks to write the domain.

One of the tricks that I usually use is to convert the domain to SQL expression, so I can run it in Postgre SQL right away to see the result. By changing the domain to SQL expression, I can analyze the domain that I write, and I can be sure it will produce a SQL expression that I expect or not. If domain that I write have produced a SQL expression that I expect, of course I just have to compare the query results from the SQL expression with the original odoo query results, are they the same or not.

I made a custom addon to do that, and I’ve uploaded this domain into SQL addon on Github, please download here. Then install it to follow this tutorial.

Before writing a domain, make sure we can make the correct SQL expression manually. Then convert that SQL expression to a domain. Then with the help of get_query method from the domain_to_sql addon above, the domain will be converted into SQL expression again. Then, we just have to compare the SQL expression that we write from the start with the SQL expression from the get_query method.

But the method above only need to do if the conditions in the SQL expression that we made contain the OR operator. If there is no OR operator, you don’t need to bother, just write the domain directly without need to create SQL expression in Postgre SQL or test it with the domain_to_sql addon.

Suppose we have SQL expression like this.

select id 
from sale_order 
where state = 'draft' and
partner_id = 10

Because there is no OR operator, we can immediately write the domain as below, ignoring the AND operator, because in odoo the default operator is AND.

[('state','=','draft'), ('partner_id','=',10)]

As a study case, let’s say we want to find some Partner with name that contain the word of agus or some Partner with address that contain the word of street 2 and have an email that contain the word of gmail. If we write above requirement in SQL expression, we can write it like this.

select id
from res_partner
where name ilike '%agus%' or
(street ilike '%street 2%' and email ilike '%gmail%')

If we run the above SQL expression in PG Admin, the result will be like this.

Then how do we convert the SQL expression above to become an odoo domain ?

In Polish Notation there are operators and operands. There are only 2 operators in the odoo domain, namely AND with the ‘&’ symbol and OR with the ‘|’ symbol. In the SQL expression above, there are 2 operators, it is OR and AND.

While the operand is an expression that consists of 3 parts to determine the value of True or False. In the SQL expression above, there are three operands, it is name ilike ‘%agus%’, street ilike ‘%street 2%’ and email ilike ‘%gmail%’.

To create a domain write all the operators from the SQL expression above sequentially in an array, as shown below.

domain = ['|','&']

Then write the operands in tuple sequentially, as below.

domain = ['|','&',('name','ilike','agus'),('street','ilike', 'street 2'), ('email', 'ilike', 'gmail')]

Then call the get_query method to see the SQL expression result from the domain above.

domain = ['|','&',('name','ilike','agus'),('street','ilike', 'Street 2'), ('email', 'ilike', 'gmail')]
query = self.env['res.partner'].get_query(domain)
print(query)

The result will be like this.

SELECT "res_partner".id FROM "res_partner" WHERE (("res_partner"."active" = 'True')  AND  ((("res_partner"."name"::text ilike '%agus%')  AND  ("res_partner"."street"::text ilike '%Street 2%'))  OR  ("res_partner"."email"::text ilike '%gmail%')))

From the code above, it turns out that the resulting SQL expression is contain the operand that we didn’t write, namely (“res_partner”.”Active” = ‘True’). Odoo by default only displays active data, that’s why the operand above always appears. To make the operand above does not appear, its mean that we will ignore the active or inactive status of a record, we can add a active_test context with a value of False as shown below.

domain = ['|','&',('name','ilike','agus'),('street','ilike', 'Street 2'), ('email', 'ilike', 'gmail')]
query = self.env['res.partner'].with_context({'active_test': False}).get_query(domain)
print(query)

Now the code above will generate a SQL expression like this.

SELECT "res_partner".id FROM "res_partner" WHERE ((("res_partner"."name"::text ilike '%agus%')  AND  ("res_partner"."street"::text ilike '%Street 2%'))  OR  ("res_partner"."email"::text ilike '%gmail%'))

You can see that the (“res_partner”.”Active” = ‘True’) operand is no longer exist. But the AND and OR operators connected the wrong operand. This means that the domain that we write is still wrong.

If this happen try to shift the operator. Starting from the rightmost operator, the AND operator, so it will look like this.

domain = ['|', ('name','ilike','agus'), '&', ('street','ilike', 'Street 2'), ('email', 'ilike', 'gmail')]
query = self.env['res.partner'].with_context({'active_test': False}).get_query(domain)
print(query)

The result will look like this.

SELECT "res_partner".id FROM "res_partner" WHERE (("res_partner"."name"::text ilike '%agus%')  OR  (("res_partner"."street"::text ilike '%Street 2%')  AND  ("res_partner"."email"::text ilike '%gmail%')))

Now the operator’s position is correct. We just need to compare the query results from odoo via the search method with the results from the first query that we just made.

If we call the search method as below.

domain = ['|', ('name','ilike','agus'), '&', ('street','ilike', 'Street 2'), ('email', 'ilike', 'gmail')]
query = self.env['res.partner'].with_context({'active_test': False}).get_query(domain)
print('query====')
print(query)

result = self.env['res.partner'].with_context({'active_test': False}).search(domain)
print('result====')
print(result)

The result will look like this.

As you can see, in my odoo installation, partners that meet the criteria for the above domain are partners with ID of 9 and 11. Same with the SQL expression results that we create first. So the domain that we write is correct.