Model Based on SQL View

Odoo SQL View for Aggregated Data

In a standard Odoo model, data is stored in a database table automatically created by Odoo. But in some cases—like dashboards, custom reports, or analytics—you might need to aggregate data from multiple models into a single, read-only structure.

This is where a PostgreSQL SQL View comes in. You can use SQL to create a view and then map it in Odoo as a model by disabling automatic table creation.

When Should You Use an SQL View?

• When you want to aggregate or join data from multiple models.
• For performance-optimized reporting models.
• When data is only read-only and doesn't need editing.
• For dashboards or KPIs that don't require user input.

Step-by-Step: Building an SQL View Model in Odoo 18

Let’s say you have a model student.student for students, and a related student.leave model that logs student leaves. Now, you want to show how many leaves each student has taken.

1. Define the Model Without Auto Table Creation

You start by telling Odoo not to create the table for this model by setting _auto = False.

from odoo import models, fields, tools

class StudentLeaveStatistics(models.Model):
    _name = 'student.leave.statistics'
    _description = 'Student Leave Statistics'
    _auto = False  # No table is created by ORM; we will use an SQL View

2. Define the Fields as Read-Only

Since SQL views are inherently read-only, you must set all fields as readonly=True.

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

3. Create the SQL View in init()

The init() method is triggered when Odoo starts or when the module is updated. This is where you define the SQL logic to create your view.

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 sl
            GROUP BY
                sl.student_id
        )
    """)

MIN(sl.id) ensures each row has a unique ID (required by Odoo). The view returns: id, student_id, and leave_count — which must match the field names exactly.

4. Create a View to Display the Data

Once the model is ready, you can add a view (List, Pivot, or Graph) to visualize it.

Example: XML for Pivot View

<record id="view_student_leave_statistics_pivot" model="ir.ui.view">
    <field name="name">student.leave.statistics.pivot</field>
    <field name="model">student.leave.statistics</field>
    <field name="arch" type="xml">
        <pivot string="Student Leave Statistics">
            <field name="student_id" type="row"/>
            <field name="leave_count" type="measure"/>
        </pivot>
    </field>
</record>