Create and update multiple records
For newcomers in Odoo development, managing multiple queries for writing or creating numerous records is common.
Initially, let's explore the process of generating multiple records.
Odoo supports batch creation. Instead of providing a single dictionary for a record, you can present a collection of dictionaries to the batch creation methods, simplifying the process.
vals = [{'name': "Azure", 'age': '20'},
{'name': "Deco", age: 21},
{'name': "Ready", age: '19'}]
self.env['student.student].create(vals)
When handling multiple records in Odoo, caution is needed with method variations across Odoo versions. Updates follow a lazy approach, delaying database writes until necessary or when explicitly triggered with flush().
For instance:
data1 = {...}
for record in recordset:
record.write(data1)
data2 = {...}
for record in recordset:
record.write(data2)
To create multiple records in batches, utilize a list of dictionaries as the value, enabling the batch creation of records. Each record in the batch triggers an individual query, enhancing performance without creating a batch log for a single query. However, it's important to note that stacking records significantly improves overall performance.
When it comes to writing records, the framework manages most tasks. For instance, if identical data is being written to multiple records, the database can be updated with a single UPDATE query.
The framework adeptly manages scenarios where the same record is edited multiple times within a single transaction.
recordset.name= 'Name'
recordset.email= 'name@example.com'
recordset.name= 'admin'
recordset.email= 'admin@example.com'
In the provided code sample, a solitary write query is executed, resulting in values such as name=admin and email=admin@example.com. These assigned values are subsequently written in a single query, maintaining optimal performance.
Generate records through database query
While the Odoo ORM offers limited methods for dataset generation, obtaining data from the ORM can be challenging. To overcome this, data retrieval in a specific format becomes crucial, often requiring operations on the data, which may introduce latency. In cases where precision is essential, executing SQL queries against the database is an option. Refer to specific guidelines on executing SQL queries in Odoo.
In the Odoo development community, there is a perception that opting for SQL queries over ORM queries inherently enhances operational speed. However, this notion is context-dependent. For the majority of tasks, ORMs outperform raw queries, offering better speed and efficiency, mainly due to the optimized delivery of data through the recordset cache.
There are two methods to access the database from records. One involves using self._cr, and the other is specifically through the environment using self.env.cr.
Illustration of utilizing self.env.cr:
self.env.cr.execute("Select * from book_book where name like '%s'" %(search_keyword,))
Examine instances of incorporating 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 exclusively produces data that contains the string "War and Peace" in the name. The outcome can be presented in the form of either a tuple or a list.
If a dictionary format is desired for the result, the dictfetchall() method can be employed instead of fetchall(). For instance,
self.env.cr.execute("Select * from book_book where name like '%s'" % ('%War and peace%',))
result = self.env.cr.dictfetchall()
The result is presented in dictionary format. Additionally, it is feasible to retrieve a single record using either the fetchone() or dictfetchone() methods. These methods exhibit similar functionality to their counterparts, fetchall() and dictfetchall().
Grouped data
Occasionally, structured data is necessary for reports like monthly or sales reports. Manually locating and grouping records can be a laborious process. In this example, we explore how to efficiently retrieve grouped data using the read group() method.
The primary applications of the read_group() method include smart buttons and handling statistical data.
During this period, it might be pertinent to showcase the count of purchase orders on the affiliate form. Identify the customer through purchase orders and determine the quantity of purchase orders associated.
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 illustration, when the tree view exhibits the purchase_count field, it retrieves and filters purchase orders across all partners in the list. While the read_group() technique may not significantly impact small datasets, it becomes a valuable solution as data volume grows, addressing potential performance issues.
Another instance of this approach involves the utilization of just one SQL query, even when dealing with extensive 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 scenario, the read_group() method internally employs the SQL GROUP BY function. This optimization enhances the efficiency of the read group method, particularly with extensive datasets. Odoo's web client internally leverages this function for charts and grouped tree views. The Read Group method offers flexibility with various arguments to modify its behavior.
Different parameters available for utilization with the read_group method
● fields: A list of fields obtained from grouping. If aggregate functions are not employed, ensure these fields are included in the group by parameter.
● domain: Specifies how records are filtered. This is a key consideration for read group methods.
● groupby: A list of fields utilized to group records. Multiple fields can be used for sorting records.
● offset: Facilitates pagination of results, allowing the skipping of certain records.
● limit: Pagination option determining the maximum number of records to retrieve.
● lazy: Accepts boolean values; defaults to True. When set to True, it only groups results using the first field in the groupby argument.
● orderby: An optional specification order that supersedes the group's natural sort order. Refer also to search() (currently only supported for many2one fields).