Odoo 16 Development Book

Model Based on SQL View

Most often, we create a model class with defined fields. Odoo will map this to a set of database tables. In some circumstances, we might need to combine the data from various models into a single table. This could be useful for creating dashboards or reports. This will be able to create a read-only model backend by using a postgresql view rather than a database table by utilizing the postgresql database engine in Odoo.

Let's talk about it using an example. Consider a model student.student, which stores all of an educational organization's student information.

1. Make a new model and set the _auto attribute to False.

class StudentLeaveStatistics(models.Model):
       _name = 'student.leave.statistics'
       _auto = False

2. Define model fields and set the read-only attribute to False so that the Views do not enable changes that you will be unable to save because PostgreSQL Views are read-only.

student_id = fields.Many2one('student.student', string="Student",
leave_count = fields.Integer(string="Leave Count", readonly=True)

3. The next step is to define the init() method for creating the view. When the auto attribute is set to False, this method is responsible for creating the table. Otherwise, nothing will happen. The view creation query must generate a view with column names that correspond to the Model's field names.

def init(self):
       tools.drop_view_if_exists(self._cr, 'student_leave_statistics')
           create or replace view student_leave_statistics as (
                   min(sl.id) as id,
                   sl.student_id as student_id,
                   count(sl.id) as leave_count,
                   student_leave as sl
                   student_student as s ON s.id = sl.student_id
                   sl.state = 'done'

4. For a new model, you can now define the Views. To explore the data, a pivot view is incredibly useful.



Cybrosys Technologies Pvt. Ltd.
Neospace, Kinfra Techno Park
Kakkancherry, Calicut
Kerala, India - 673635



Cybrosys Technologies Pvt. Ltd.
1st Floor, Thapasya Building,
Infopark, Kakkanad,
Kochi, India - 682030.



Cybrosys Techno Solutions
The Estate, 8th Floor,
Dickenson Road,
Bangalore, India - 560042

Send Us A Message