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.