Odoo 16 Development Book

Working with read_group()

We may occasionally need to aggregate records from a database in order to retrieve them. In such cases, the read_group() method can be used to obtain aggregate results. Using the read group() method, for example, we can obtain the average price of the previous month's purchases.

Using an example, let's talk about the method. Consider a model student.student who keeps track of all student records.

from odoo import fields, models
class Student(models.Model):
   _name = "student.student"
   _description = "Student"
   name = fields.Char(string="Name", required=True)
   phone = fields.Char(string="Phone Number")
   email = fields.Char(string="Email")
   category_id = fields.Many2one('student.category', string="Category")
   partner_id = fields.Many2one('res.partner', string="Partner", required=True)
   date = fields.Date(string="Date", default=fields.Date.today())
   total_grade = fields.Float(string="Total Grade")

Create another model to save all Student Category records.

from odoo import fields, models
class StudentCategory(models.Model):
   _name = "student.category"
   _description = "Student Category"
   name = fields.Char(string="Name", required=True)
   description = fields.Text(string="Description")

Define a new method _get_average_score(), to get the average score of students who use the read_group() method and are grouped by category.

def get_average_cost(self):
   grouped_result = self.read_group(
       [('total_grade', "!=", False)], #domain
       ['category_id', 'total_grade:avg'], #fields
       ['category_id'] #group_by
   )
   return grouped_result

The group_by() method retrieves data using SQL group by and aggregate functions. In the example method, three parameter values are passed.

Domain: This domain was used to filter records based on this. Condition. Only students who have a value in the total_grade field are considered for rest operations in this example.
Fields: List of fields to retrieve from the record. The field parameter's possible values are listed below.

field_name: The name of the field to be included in the final result. For the group_by parameter, all fields passed as a single field_name must also be added. Otherwise, an error message will be displayed.
field_name:agg: You can pass a field name as an aggregate parameter. This link will take you to a list of aggregate functions.postgresql
○ name:agg(field_name): This is the same as before. But, with this syntax, you can give the column aliases, such as average_grade:avg('total_grade')

Group by: You can pass a list of fields as the group by argument. The result will be grouped using these fields. When working with date type or datetime type fields, there are some changes. To apply the date grouping based on the various time durations, like date release: month, you can pass the group by function for something like the date and datetime column. This will apply grouping based on the months.

There are a few more parameters that can be passed.

Offset: The optional number of records can skip.
Limit: Return a maximum number of records.
Order by: Which field determines how the result is ordered.
Lazy: Boolean type argument that can be passed, which is set as True by default. Its results are still only grouped by the first group by if true is passed, and the remaining group bys are stored in the __context key. If false, in one call, all the group bys are done.

Book Preferences

Odoo 16 Development Book

Working with read_group()