Create and update multiple records
If you are new to Odoo development, you may need to run multiple queries to write or create multiple records. In this case, you can see how records are created and written.
First, let's see how to create multiple records.
Odoo allows you to create records in batches. Just provide a dictionary with field values when generating a single record. Instead of passing a single dictionary, you can pass a collection of these dictionaries to create records in batch creation methods.
vals = [{'name': "Azure",'age': '20',},
{'name': "Deco",age: 21},
{'name': "Ready",age: '19',}]
self.env['student.student].create(vals)
Writing multiple records.
If you are working with different versions of Odoo, you should be careful how the methods are written in this case. A lazy approach is used for updates. This means that data is not written to the database immediately. Odoo writes data to the database only when needed or when flush() is called.
data1 = {...}
for record in recordset:
record.write(data1)
data2 = {...}
recordset.write(data2)
To create multiple records in batches, you need to use the value dictionary in list format. This results in the batch creation of records.
When generating records in a batch, a query is automatically inserted for each record. This means that no batch log is created for a single query. However, this does not negate the fact that stacking records improves performance.
When it comes to writing, things are a little different. The framework does most of the work. For example, if you write identical data to all data records, you can update the database with a single UPDATE query.
Even if you edit the same record multiple times in the same transaction, the framework will handle it.
recordset.name= 'Name'
recordset.email= 'name@example.com'
recordset.name= 'admin'
recordset.email= 'dmin@example.com'
In the sample code, only one write query is executed, and the resulting values are name=admin and email=admin@example.com. The assigned values are later written in a single query, which does not negatively affect performance.
Generate records through database query
Odoo ORM has limited ways to generate datasets, but it can be difficult to get data from ORM. In such cases, you can retrieve the data in a specific format. To get a specific result, you have to perform an operation on the data. This slows it down. You can run SQL queries against your database to handle these special cases. In this case, you should check here how to execute SQL queries from Odoo.
Odoo developers believe that running SQL queries instead of ORM queries speeds up operations. That's not entirely correct. It depends. ORMs are better and faster than RAW queries for most tasks because the data is delivered via the recordset cache.
There are two ways to access the database from records. One using self._cr and one specifically from the environment using self.env.cr.
Example for using self.env.cr
self.env.cr.execute("Select * from book_book where name like '%s'" %(search_keyword,))
Discuss examples of how to use self._cr in Python code.
self.env.cr.execute("Select * from book_book where name like '%s'" % ('%War and peace%',))
result = self.env.cr.fetchall()
It just outputs the data you can get if the name contains the string War and peace. The result can be in the form of a tuple or list.
If we want the result as a dictionary format, we can use dictfetchall() method instead of fetchall(), for example,
self.env.cr.execute("Select * from book_book where name like '%s'" % ('%War and peace%',))
result = self.env.cr.dictfetchall()
The output is in the form of a dictionary. It is also possible to retrieve only a single record. You can use the fetchone() or dictfetchone() methods. This method has the same functionality as the corresponding fetchall() and dictfetchall() methods.
Grouped data
Sometimes, you need data in keyed forms, such as monthly reports, sales reports, etc. Finding and grouping records manually are time-consuming. In this recipe, we'll see how to retrieve grouped data using the read group() method.
The read_group() method is primarily used for smart buttons and statistical data.
At that time, we may display the number of sales orders on the affiliate form. Find the customer by sales order and find the number of sales orders.
purchase_count = fields.Integer(compute='_compute_purchase_count',
string='Purchase count')
def _compute_purchase_count(self):
po = self.env['purchase.order'].search(domain=[('partner_id', 'in', self.ids)])
for partner in self:
partner.purchase_count = len(po.filtered(lambda purchase: purchase.partner_id.id == partner.id))
In this example, when the purchase_count field is displayed in the tree view, we can see that the Purchase orders are retrieved and filtered against all partners in the list. The read_group() technique doesn't make much of a difference for modest amounts of data, but as the amount of data increases, this can become a problem. The read_group method solves this problem.
There is another example of this, but it only consumes one SQL query, even for large datasets.
purchase_count = fields.Integer(compute='_compute_purchase_count',
string='Purchase count')
def _compute_purchase_count(self):
po_data = self.env['purchase.order'].read_group(
domain=[('partner_id', 'in', self.ids)],
fields=['partner_id'], groupby=['partner_id'])
mapped_data = dict([(p['partner_id'][0], p['partner_id_count']) for p in po_data])
for partner in self:
partner.purchase_count = mapped_data[partner.id]
In this example, the read group() method uses the SQL GROUP BY function internally. This can speed up the read group method even with very large data sets. The Odoo web client uses this function internally for charts and grouped tree views. Various arguments can be used to change the behavior of the Read Group method.
Various parameters that can be used with the read_group method
● fields: This is the list of fields obtained from grouping. If you are not using aggregate functions, you must include the following fields in the group by parameter.
● domain: This is how the records are filtered. This is where to look for reading group methods.
● groupby: This option is a list of fields used to group records. You can sort records by multiple fields.
● offset: This option is used for the pagination of results. This argument is useful for skipping some records.
● limit: This is a pagination option that specifies the maximum number of records to retrieve.
● lazy: You can use boolean values for this parameter. Its value is True by default. Setting this parameter to True will only group the results using the first field in the groupby argument.
● orderby: An optional specification order that overrides the group's natural sort order. See also search() (currently only supported for many2one fields).