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",
                                readonly=True)
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')
       self._cr.execute("""
           create or replace view student_leave_statistics as (
               select
                   min(sl.id) as id,
                   sl.student_id as student_id,
                   count(sl.id) as leave_count,
               from
                   student_leave as sl
               join
                   student_student as s ON s.id = sl.student_id
               where
                   sl.state = 'done'
           )""")

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

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