Chapter 4 - Odoo 15 Development Book

Raw SQL Queries

Raw SQL Queries:

In most cases it is possible to perform search() method for fetching record set based on conditions. Domain is used to filter the record based on condition. But some times it will not be possible to filter the required set of records using search(). One of the reason will be the difficulty in expressing the condition using search domain.

Using raw SQL queries is a solution for this problem. Let’s discuss it with the example. Consider a model student.student which 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")
   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, lets try fetching the Name, Total Grade and Email of all students along with the Partner name related to the student where Total grade is greater than 50. For that, define a function get_top_score_students(self) and give the raw SQL query which 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 for executing the query

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

For easiness here the result is printed. Now lets see the result printed. Test the method by creating 2 simple student records.

The printed result for this 2 records is shown below.

RESULT :  [('Student Azure', 60.0, 'azure@student.in', 'Azure Interior')]