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.
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')]