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.

odoo-development odoo-development

Let’s Print the result.

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

whatsapp
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