Most of the time, you can perform the operations you want using the search()
method. However, sometimes, you need more—either you cannot express what you want using the domain syntax, for which some operations are tricky if not downright impossible, or your query requires several calls to search()
, which ends up being inefficient.
This recipe shows you how to use raw SQL queries to read res.partner
records grouped by country.
We will be using a simplified version of the res.partner
model:
class ResPartner(models.Model): _name = 'res.partner' name = fields.Char('Name', required=True) email = fields.Char('Email') is_company = fields.Boolean('Is a company') parent_id = fields.Many2one('res.partner', 'Related Company') child_ids = fields.One2many('res.partner', 'parent_id', 'Contacts') country_id = fields.Many2one('res.country', 'Country')
To write a method that returns a dictionary that contains the mapped names of countries to a recordset of all active partners from that country, you need to perform the following steps:
res.partner
:class ResPartner(models.Model): _inherit = 'res.partner'
partners_by_country()
:@api.model: def partners_by_country(self):
sql = ('SELECT country_id, array_agg(id) ' 'FROM res_partner ' 'WHERE active=true AND country_id IS NOT NULL ' 'GROUP BY country_id')
self.env.cr.execute(sql)
country_model = self.env['res.country'] result = {} for country_id, partner_ids in self.env.cr.fetchall(): country = country_model.browse(country_id) partners = self.search( [('id', 'in', tuple(partner_ids))] ) result[country] = partners return result
In step 3, we declare an SQL SELECT
query. It uses the id
field and the country_id
foreign key, which refers to the res_country
table. We use a GROUP BY
statement so that the database does the grouping by country_id
for us, and the array_agg
aggregation function. This is a very useful PostgreSQL extension to SQL that puts all the values for the group in an array, which Python maps to a list.
Step 4 calls the execute()
method on the database cursor stored in self.env.cr
. This sends the query to PostgreSQL and executes it.
Step 5 uses the fetchall()
method of the cursor to retrieve a list of rows selected by the query. From the form of the query we executed, we know that each row will have exactly two values, the first being country_id
and the other one, the list of ids
for the partners having that country. We loop over these rows and create recordsets from the values, which we store in the result dictionary.
The object in self.env.cr
is a thin wrapper around a psycopg2
cursor. The following methods are the ones you will want to use most of the time:
execute(query, params)
: This executes the SQL query
with the parameters marked as %s
in the query substituted with the values in params, which is a tuplefetchone()
: This returns one row from the database, wrapped in a tuple (even if there is only one column selected by the query)fetchall()
: This returns all the rows from the database as a list of tuplesfetchalldict()
: This returns all the rows from the database as a list of dictionaries mapping column names to valuesBe very careful when dealing with raw SQL queries:
search([('id', 'in', tuple(ids)])
with any list of ids
you are retrieving to filter out records to which the user has no access to.NOT NULL
, UNIQUE
, and FOREIGN KEY
constraints, which are enforced at the database level. So are any computed field recomputation triggers, so you may end up corrupting the database.