Odoo 17 Development Book

Working with read_group()

Sometimes, it becomes necessary to aggregate data from a database for retrieval purposes. In such instances, the read_group() method proves valuable for obtaining aggregated results.

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

class StudentStudent(models.Model):
    _name = "student.student"
    _description = "Student"

    name = fields.Char(string="Name", required=True)
    category_id = fields.Many2one('student.category', 
                                  string="Category")
    total_grade = fields.Float(string="Total Grade")

Create another model to save all Student Category records.

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

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 is employed to fetch data utilizing SQL's GROUP BY clause along with aggregate functions. In this particular example, three parameter values are provided.

● Domain: The domain is utilized to filter records based on a specific condition. In this example, only students who have a value in the total_grade field are taken into consideration for subsequent operations.

● Fields: List of fields to retrieve from the record. The possible values for the field parameter are given below.

● field_name: The name of the fields to be included in the final result. For the group_by parameter, all fields passed as a single field_name must also be added. Failure to do so will result in an error message.

● field_name:agg: You have the option to pass a field name as an aggregate parameter.

● 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: The boolean type argument you can pass is set as True by default. When set to True, the results are grouped only by the first group by, and the remaining group bys are stored in the __context key. If set to False, all the group bys are performed in one call.