Model Based on SQL View
Typically, we will create a model class with defined fields. Odoo will convert this to a collection of database tables. In some cases, we may need to integrate data from multiple models into a single database. This could come in handy for designing dashboards or reports. Using the postgresql database engine in Odoo, this will be able to establish a read-only model backend by using a postgresql view rather than a database table.
Let's have a look at an example. Consider a good example of a student.student, which stores all of the student information in an educational organisation.
1. Create a new model with the _auto attribute set 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 modifications that you will be unable to save due to the read-only nature of PostgreSQL Views.
student_id = fields.Many2one('student.student', string="Student",readonly=True)
leave_count = fields.Integer(string="Leave Count", readonly=True)
3. The init() function for constructing the view is then defined. This method is in charge of constructing the table when the auto attribute is set to False. Nothing will happen otherwise. The view generation query must create a view with column names that match the field names in the Model.
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)""")
4. You may now specify the Views for a new model. A pivot view is quite handy for exploring data.