Build Powerful Reports

Firstly, Odoo SQL Views let you handle complex data without changing your base tables. Moreover, they act like virtual tables that simplify joins and reports. Therefore, you can boost performance in heavy queries. Next, you will learn step by step how to create both standard and materialized SQL views in Odoo. Additionally, you will see code examples and explanations that help you master this feature. Finally, you will discover best practices, use cases, and troubleshooting tips that ensure your modules run smoothly.

source : https://www.linkedin.com/pulse/sql-views-en-odoo-luis-a-pinz%C3%B3n-dh3ge/

Understanding Odoo SQL Views and Their Types

Firstly, you must know that a SQL view in Odoo serves as a read-only table built from a SELECT query. Moreover, PostgreSQL supports normal views, materialized views, recursive views, and even updatable views. Therefore, you can pick the right view type for your needs. Next, let’s explore each view type briefly:

What Are Standard SQL Views in Odoo Modules?

Firstly, standard views generate at runtime and always show fresh data. Moreover, they never store results on disk, so they cost minimal storage. However, they may run slower for heavy queries. Therefore, you should use them for real-time data needs and lightweight reports.

What Are Materialized SQL Views in Odoo?

Firstly, materialized views store query results physically. Moreover, they speed up heavy and repeated reports by caching data. However, they require manual or scheduled refresh jobs. Therefore, you should use them for dashboards or historical reports that tolerate some lag.

Advantages of Using Odoo Database Views

Firstly, you simplify complex joins by defining a view once. Moreover, you avoid repeating JOIN statements across multiple modules. Therefore, you reduce code duplication and potential bugs. Next, you improve maintainability by exposing only the fields you need. Additionally, you boost security by hiding internal tables. Furthermore, you enhance performance with materialized views when needed.

Optimize Complex Joins Effortlessly

Firstly, you can include multiple tables in one view. Moreover, you avoid writing the same JOINs in different methods. Therefore, you speed up development and keep code clean.

Simplify Reporting in Odoo

Firstly, you build consolidated views that match business needs. Moreover, you show data in Odoo’s UI like any other model. Therefore, you enable end users to filter, search, and export reports easily.

Enhance Security and Maintainability

Firstly, you expose only necessary columns to users. Moreover, you prevent direct access to underlying tables. Therefore, you control data access and reduce risks.

How to Create Standard SQL Views in Odoo

Firstly, you define a model with _auto = False. Moreover, you override its init() method to execute a CREATE VIEW statement. Next, you register the model in Odoo and use it like any other model in XML views.

Define the Model with _auto = False

Firstly, you create a Python class under your module’s models folder. Moreover, you set _name, _description, and _auto = False to prevent automatic table creation. Therefore, Odoo will not create a physical table for this model.

Python Code for SaleReportView

from odoo import models, fields, tools

class SaleReportView(models.Model):
    _name = 'sale.report.view'
    _description = 'Sales Report View'
    _auto = False  # Prevents automatic table creation

    sale_id = fields.Many2one('sale.order', string='Sale Order')
    product_id = fields.Many2one('product.product', string='Product')
    quantity = fields.Float(string='Quantity Sold')
    total = fields.Float(string='Total Amount')

    def init(self):
        tools.drop_view_if_exists(self.env.cr, self._table)
        query = """
            CREATE OR REPLACE VIEW sale_report_view AS (
                SELECT 
                    sol.id AS id,
                    so.id AS sale_id,
                    sol.product_id AS product_id,
                    sol.product_uom_qty AS quantity,
                    sol.price_total AS total
                FROM sale_order_line sol
                JOIN sale_order so ON sol.order_id = so.id
            )
        """
        self.env.cr.execute(query)

Explanation of SaleReportView Code

Firstly, the class inherits from models.Model. Moreover, _auto = False stops Odoo from creating a table. Next, sale_id, product_id, quantity, and total define view fields. Then, init() drops any existing view with the same name. Finally, the CREATE OR REPLACE VIEW query builds the view using a SQL SELECT statement. Therefore, you can read this view like a normal model.

Register the View in XML

Firstly, you add an XML record under views/ in your module. Moreover, you use the <record> tag for ir.ui.view. Then, you define a tree or form that uses your view model.

XML Code for Tree View

<odoo>
  <record id="view_sale_report_tree" model="ir.ui.view">
    <field name="name">sale.report.view.tree</field>
    <field name="model">sale.report.view</field>
    <field name="arch" type="xml">
      <tree>
        <field name="sale_id"/>
        <field name="product_id"/>
        <field name="quantity"/>
        <field name="total"/>
      </tree>
    </field>
  </record>
</odoo>

Explanation of XML Code

Firstly, the model attribute matches _name in Python. Moreover, the <tree> tag builds a list view. Next, each <field> tag maps to a view field. Therefore, Odoo will render this view under the module’s menu.

How to Build Materialized SQL Views in Odoo

Firstly, you follow a similar process for materialized views. Moreover, you replace CREATE VIEW with CREATE MATERIALIZED VIEW. Then, you add a method to refresh the view and schedule it via cron.

Define the Materialized Model

Firstly, you create a Python class under models/. Moreover, you set _auto = False again. Then, you override init() to create the materialized view.

Python Code for MaterializedSaleReport

from odoo import models, fields, tools

class MaterializedSaleReport(models.Model):
    _name = 'materialized.sale.report'
    _description = 'Materialized Sales Report'
    _auto = False  # Prevents table creation

    sale_id = fields.Many2one('sale.order', string='Sale Order')
    product_id = fields.Many2one('product.product', string='Product')
    quantity = fields.Float(string='Quantity Sold')
    total = fields.Float(string='Total Amount')

    def init(self):
        tools.drop_view_if_exists(self.env.cr, self._table)
        query = """
            CREATE MATERIALIZED VIEW materialized_sale_report AS (
                SELECT 
                    sol.id AS id,
                    so.id AS sale_id,
                    sol.product_id AS product_id,
                    sol.product_uom_qty AS quantity,
                    sol.price_total AS total
                FROM sale_order_line sol
                JOIN sale_order so ON sol.order_id = so.id
            )
        """
        self.env.cr.execute(query)

    def refresh_view(self):
        self.env.cr.execute("REFRESH MATERIALIZED VIEW materialized_sale_report")

Explanation of MaterializedSaleReport

Firstly, the class matches the standard view structure. Moreover, you use CREATE MATERIALIZED VIEW to store data physically. Next, refresh_view() runs REFRESH MATERIALIZED VIEW to update data. Therefore, you must call this method to see new data.

Refreshing the Materialized SQL View

Firstly, you can call refresh_view() manually in shell mode. Moreover, you can automate it with a cron job. Next, you add an XML record under data/ to schedule a daily refresh.

Cron Job Configuration

<odoo>
  <record id="ir_cron_refresh_materialized_view" model="ir.cron">
    <field name="name">Refresh Materialized Sales Report</field>
    <field name="model_id" ref="model_materialized_sale_report"/>
    <field name="state">code</field>
    <field name="code">model.refresh_view()</field>
    <field name="interval_number">1</field>
    <field name="interval_type">days</field>
    <field name="numbercall">-1</field>
  </record>
</odoo>

Explanation of Cron Settings

Firstly, ir.cron defines scheduled tasks in Odoo. Moreover, interval_number and interval_type set the frequency. Next, state="code" lets you run Python code directly. Then, model.refresh_view() calls your refresh method. Therefore, your materialized view updates every day automatically.

Best Practices for Odoo SQL Views

Firstly, you should keep views narrow and focused. Moreover, you must avoid selecting unnecessary columns. Therefore, you reduce memory and CPU load. Next, you should test view performance under real-world data. Additionally, you should add indexes on underlying tables when needed. Furthermore, you should monitor query plans using PostgreSQL’s EXPLAIN tool. Finally, you should document each view in your module for maintainers.

Keep Views Simple and Focused

Firstly, define clear purposes for each view. Moreover, split complex logic into multiple views if needed. Therefore, you ease debugging and updates.

Monitor Performance Regularly

Firstly, use EXPLAIN ANALYZE to check execution plans. Moreover, you can tune queries by adding filters or indexes. Therefore, you maintain fast response times.

Common Use Cases for Odoo Database Views

Firstly, you use Odoo SQL Views for real-time dashboards. Moreover, you use them for consolidated reports across sales, purchases, and inventory. Next, you use materialized views for monthly sales summaries. Additionally, you integrate external BI tools like Metabase via views. Therefore, you leverage your data without extra ETL.

Real-Time Dashboard Reporting

Firstly, define a view that joins sales orders, invoices, and stock moves. Moreover, expose key KPIs like total sales and open orders. Therefore, you deliver real-time insights to managers.

Historical Data Analysis

Firstly, use a materialized view that stores daily snapshots. Moreover, schedule a nightly refresh. Therefore, you support trend analysis without slowing down live transactions.

Tips for Optimizing SQL View Performance

Firstly, limit rows returned by adding WHERE clauses in your view. Moreover, push filters down to the base query. Therefore, you avoid scanning full tables. Next, use materialized views for heavy aggregations. Additionally, store pre-computed values instead of computing them on the fly. Finally, archive old data in separate tables to speed up current reports.

Troubleshooting Odoo SQL Views

Firstly, check for syntax errors if view creation fails. Moreover, verify that your model’s _table matches the view name. Next, ensure your database user has CREATE VIEW privileges. Additionally, clear old views with tools.drop_view_if_exists. Therefore, you avoid conflicts during module updates.

View Creation Errors

Firstly, look at Odoo’s server logs for SQL errors. Moreover, run your query in psql to debug faster. Therefore, you catch issues early.

Permission Issues

Firstly, ensure your Odoo database user can access underlying tables. Moreover, grant SELECT on those tables if needed. Therefore, you prevent access errors in production.

Conclusion: Mastering Odoo SQL Views

Firstly, you now know how to build standard and materialized SQL views in Odoo. Moreover, you can simplify complex reports, boost performance, and improve security. Therefore, you can deliver better modules faster. Next, apply best practices and monitor performance regularly. Additionally, explore more in the Odoo Documentation. Finally, master Odoo SQL Views to unlock your data’s full potential.

FAQ

Can I update a standard SQL view in Odoo?

Firstly, you cannot INSERT or UPDATE into standard views directly. Moreover, you must manage data via base models. Therefore, use views for reporting only.

How often should I refresh materialized views?

Firstly, you should refresh materialized views based on your data change rate. Moreover, daily or hourly schedules work for most cases. Therefore, choose a frequency that balances freshness and performance.

Where can I learn more about PostgreSQL views?

Firstly, you can visit the PostgreSQL Views Documentation. Moreover, it covers advanced topics like updatable and recursive views.