Raw SQL Queries

In most cases, Odoo developers retrieve records using the search() method combined with domain filters. This method works well for standard use cases and keeps your code ORM-compliant.

However, there are situations where domain expressions become too complex or limiting. In such cases, raw SQL queries can offer better flexibility and performance.

When to Use Raw SQL

Use raw SQL when:

• Domain filters can't express the logic easily (e.g., joins, aggregations).
• You need performance optimization for complex multi-model queries.
• You want fine-grained control over the SQL logic.

Example: Fetch Top Scoring Students

Let’s say you’re working with a custom model called student.student that holds student data.
Here’s a basic definition of the model:

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 say you want to get a list of:

• Student name
• Total grade
• Email
• Partner name (linked via partner_id)

But only for those students whose total_grade is greater than 50.
This can be done efficiently using a raw SQL query.

Defining the Raw SQL Function

You can define a method like this in your model:

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()
    return result

• self.env.cr.execute(sql_query) executes the SQL query.
• self.env.cr.fetchall() fetches all rows from the result.
• Each row is returned as a tuple of values, like: ('Student Name', Total Grade, 'Email', 'Partner Name')

Testing the Method

Suppose you’ve created two student records:

• Azure with a total grade of 51
• John with a total grade of 40

Now, run the method:

results = self.get_top_score_students()
print(results)

Expected Output:

[('Azure', 51.0, 'azure@gmail.com', 'Azure Interior')]

This means the method has successfully filtered and retrieved only the student(s) with a grade above 50, along with their email and partner name.