Odoo 17 Development Book - Raw sql queries

Raw SQL Queries

Typically, the search() method is used to retrieve a record set based on conditions. The domain is used to filter the record based on the condition. However, using the search() method to filter the required set of records is not always possible. One reason is that expressing the condition using the search domain is difficult.

This issue can be solved with raw SQL queries.

Consider the following example.

Consider a student.student model that 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")

Let's see if we can get all students' names, total grades, and emails, as well as the Partner name associated with the student whose total grade is greater than 50. Create a function called get_top_score_students(self) and pass in 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()

To run the query, use

self.env.cr.execute(sql_query).

Use

self.env.cr.fetchall()

to retrieve the result.

The outcome is printed here for convenience. Let's take a look at the results now. To put the method to the test, create two simple student records.

Let’s Print the result.

Result: [('Azure', 51.0, 'azure@gmail.om', 'Azure Interior')]