Odoo 16 Development Book

Raw SQL Queries:

The search() method can typically be used for retrieving a record set based on conditions. The record is filtered based on condition using the domain. However, it is not always possible to use search() method to filter the required set of records. One reason is the difficulty in expressing the condition using the search domain.

This problem can be solved by using raw SQL queries.

Let's take a look at an example.

Consider a model student.student, which contains all of 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")
   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")

Now, let's see if we can get the Name, Total Grade, and Email of all students, as well as the Partner name associated with the student whose Total Grade is greater than 50. Define a function get_top_score_students(self) and provide the raw SQL query that will select all required fields.

def get_top_score_students(self):
   sql_query = """ SELECT s.name, s.total_grade, s.email, p.name
                   FROM student_student AS s
                   JOIN
                       res_partner AS p ON p.id = s.partner_id
                   WHERE
                       s.total_grade > 50
   """
   self.env.cr.execute(sql_query)
   result = self.env.cr.fetchall()

Use self.env.cr.execute(sql_query) for executing the query.

To fetch the result, use self.env.cr.fetchall()

The result is printed here for easiness. Let's look at the output now. Create two simple student records to test the method.

odoo Development odoo Development

Below is a printout of the results for these two records.

TOP SCORE: [(14, 60.0, 'azure@gmail.com', 'Azure Interior')]
AVERAGE SCORE: [(60, 30.0, 'waesyuilj@gmail.com', 'Brandon Freeman')]
location

Calicut

Cybrosys Technologies Pvt. Ltd.
Neospace, Kinfra Techno Park
Kakkancherry, Calicut
Kerala, India - 673635

location

Kochi

Cybrosys Technologies Pvt. Ltd.
1st Floor, Thapasya Building,
Infopark, Kakkanad,
Kochi, India - 682030.

location

Bangalore

Cybrosys Techno Solutions
The Estate, 8th Floor,
Dickenson Road,
Bangalore, India - 560042

Send Us A Message