Chapter 4 - Odoo 15 Development Book

Working with read_group()

Working with read_group():

There may be some use cases where we need to fetch records from database by aggregating the records. read_group() () method can be used in such cases to get aggregate results. For example, we can get the average price of the previous month’s purchases by using the read_group() method.

Let us discuss the method with the help of an example. Consider a model student.student that stores all the 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 records created for Student Category.

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 grouped by category which use the read_group() method.

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 use SQL group by and aggregate functions to fetch the data. Here in the example method 3 parameter values are passed.

• Domain: The domain used to filter out records based on this condition. In this example, only the students with a value in total_grade field will be considered for rest operations.
• Fields: List of fields that need to fetch from the record. Following are the possible values of field parameter
• field_name: The field name itself to include in the final result. All fields passed like single field_name must also be added for the group_by parameter. Otherwise, it will raise an error.
• field_name:agg: Field name can be passed with aggregate parameter.The list of aggregate functions can be found from the following link.postgresql
• ○ name:agg(field_name) : This is the same as the previous one, but, with this syntax, you can give column aliases, such as average_grade:avg('total_grade')
• Group by : List of fields can be passed as group_by argument. These fields will be used to group the result. There is some change when dealing with date type or datetime type fields. For the date and datetime column, you can pass groupby_function to apply date grouping based on different time durations, such as date_release:month . This will apply grouping based on months.

There are some more optional parameters that can be passed.

• Offset : Optional number of records to skip.
• Limit : Maximum number of records to return.
• Order by : Based on which field the result is to be ordered.
• Lazy : Boolean type argument can be passed, which is set as True by default. If true is passed, the results are only grouped by the first groupby, and the remaining groupbys are put in the __context key. If false, all the groupbys are done in one call.