On my previous article, I have written that : write domain on odoo is not easy. Therefore we must have some tricks to handle it. In that article, I have written one of the tricks I usually use, which is to change the domain to SQL Query, so we can analyze it.

This time I will write the second trick that I usually use. That is by using the odoo.osv.expression module. If this trick is combined with the first one, writing domains on odoo should not be a difficult job anymore.

To follow this tutorial, make sure you’ve read the first trick above, and make sure you’ve installed the domain_to_sql module. Then import the expression module with the code below.

from odoo.osv import expression

For example, let’s say we have a query like this.

SELECT
	id
FROM res_partner
WHERE 
	name ilike '%azure%' OR
	email ilike '%gmail%'

What we need to do is, write each condition in the where-clause above into a separate domain. The query above has 2 conditions, namely name ilike ‘%azure%’ and email ilike ‘%gmail%’. So we can write the domain for the 2 conditions above like in the code below.

first_condition = [('name','ilike','azure')]
second_condition = [('email','ilike','gmail')]

After we finish writing each condition in the where-clause into a separate domain, the next step we need to do is to combine all the above domains/conditions into a single domain by using the expression module.

There are 2 functions belonging to the expression module that we can use to write domains easily. It is the AND function and the OR function. The SQL Query above, both conditions are glued with the OR operator, so, we can use the OR function belonging to the expression module to combine the two domains that we have been written into a single domain. Look at the code below.

first_condition = [('name','ilike','azure')]
second_condition = [('email','ilike','gmail')]

domain = expression.OR([first_condition, second_condition])
print('The final domain ======')
print(domain)

query = self.env['res.partner'].with_context({'active_test': False}).get_query(domain)
print('The SQL Query generated =======')
print(query)

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

And here is the result of the code above on my computer.

The second example, let’s say we have a query like the code below.

SELECT
	id
FROM res_partner
WHERE 
	mobile ilike '%62%' OR
	(
		name ilike '%azure%' AND
		email ilike '%gmail%'
	)

The query above has three conditions, where the second and third conditions are written in parentheses, which means that the two conditions are in one block. So, how to convert the query above into a domain with the expression module?

The first step we have to do is to write all the conditions into a separate domain, like the previous example.

first_condition = [('mobile','ilike','62')]        
second_condition = [('name','ilike','azure')]
third_condition = [('email','ilike','gmail')]

Then combine the conditions in parentheses (in a block) into a separate domain. In the query above, the second and third conditions are in parentheses, so, we need to combine them into a separate domain.

second_plus_third_condition = expression.AND([second_condition, third_condition])

If all conditions in parentheses have been combined into a separate domain, the last step we need to do is, merge all domains into a single final domain, like the code below.

first_condition = [('mobile','ilike','62')]        
second_condition = [('name','ilike','azure')]
third_condition = [('email','ilike','gmail')]

second_plus_third_condition = expression.AND([second_condition, third_condition])

domain = expression.OR([first_condition, second_plus_third_condition])
print('The final domain ======')
print(domain)

query = self.env['res.partner'].with_context({'active_test': False}).get_query(domain)
print('The SQL Query generated =======')
print(query)

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

The code above if executed on my computer, the result will look like the image below.

If we tidy up the generated query, it will look like this.

SELECT 
	"res_partner".id 
FROM "res_partner" 
WHERE 
	(
		("res_partner"."mobile"::text ilike '%62%') OR 
		(
			("res_partner"."name"::text ilike '%azure%') AND 
			("res_partner"."email"::text ilike '%gmail%')
		)
	)

What do you think? The generated query is the same as the query that has been prepared as the example, right?

After learning these two easy domain writing tricks, I hope you won’t have any trouble writing any complex domains again.

The last one, the question is, if we have got the domain that we want, and it has been tested and proven that the results are correct, do we need to remove the domain generation code with the expression module and leave the final domain only like the code below?

domain = ['|', ('mobile', 'ilike', '62'), '&', ('name', 'ilike', 'azure'), ('email', 'ilike', 'gmail')]

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

The decision is up to you.