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:
- Write a class extending
res.partner:class ResPartner(models.Model): _inherit = 'res.partner' - Add a method called
partners_by_country():@api.model: def partners_by_country(self): - In the method, write the following SQL query:
sql = ('SELECT country_id, array_agg(id) ' 'FROM res_partner ' 'WHERE active=true AND country_id IS NOT NULL ' 'GROUP BY country_id') - Execute the query:
self.env.cr.execute(sql)
- Iterate over the results of the query to populate the result dictionary:
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 SQLquerywith the parameters marked as%sin the query substituted with the values in params, which is a tuple
fetchone(): 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 values
Be very careful when dealing with raw SQL queries:
- You are bypassing all the security of the application. Be sure to call
search([('id', 'in', tuple(ids)])with any list ofidsyou are retrieving to filter out records to which the user has no access to. - Any modification you are making is bypassing the constraints set by the addon modules, except the
NOT NULL,UNIQUE, andFOREIGN KEYconstraints, which are enforced at the database level. So are any computed field recomputation triggers, so you may end up corrupting the database.
- For access rights management, see Chapter 10, Access Security.