How to Implement SQL Views (_auto = False) in Odoo?
✅ What is _auto = False?
_auto = False
🧪 Why Use SQL Views in Odoo?
- To create reporting models for dashboards and KPIs.
- For complex data aggregations (e.g., sums, joins) that are faster in SQL.
- To expose read-only computed data to Odoo UI, exports, or APIs.
📌 Realistic Examples : Sales Summary by Customer
1. Define the Model Using _auto = False
class SaleReportCustomer(models.Model):
_name = ‘sale.report.customer’
_description = ‘Sales Report by Customer’
_auto = False
_rec_name = ‘partner_id’
partner_id = fields.Many2one(‘res.partner’, string=‘Customer’)
total_amount = fields.Float(string=‘Total Sales Amount’)
order_count = fields.Integer(string=‘Number of Orders’)
def init(self):
tools.drop_view_if_exists(self._cr, self._table)
self._cr.execute(f“””
CREATE OR REPLACE VIEW {self._table} AS (
SELECT
row_number() OVER () AS id,
s.partner_id,
COUNT(s.id) AS order_count,
SUM(s.amount_total) AS total_amount
FROM sale_order s
WHERE s.state IN (‘sale’, ‘done’)
GROUP BY s.partner_id
)
“””)
⚠️ Key Points
Concept |
Details |
| _auto = False | Prevents Odoo from creating a DB table. |
| init() method | Used to manually create SQL view via raw SQL. |
| tools.drop_view_if_exists() | Utility to safely recreate views on module upgrade. |
| id field | Must be present and unique in the view for Odoo to work properly. |
🧪 Usage in Odoo
You can now use this model:
- In tree views and pivot tables for custom dashboards.
- For exporting summarized data.
- For KPI widgets on custom dashboards.
<record id=”view_sale_report_customer_tree” model=”ir.ui.view”>
<field name=”name”>sale.report.customer.tree</field>
<field name=”model”>sale.report.customer</field>
<field name=”arch” type=”xml”>
<tree>
<field name=”partner_id”/>
<field name=”order_count”/>
<field name=”total_amount”/>
</tree>
</field>
</record>
✅ Summary Table
Feature |
Behavior |
| _auto = False | Tells Odoo not to create a DB table |
| SQL view | Must be created manually via init() |
| Read-only model | Cannot use create(), write(), or unlink() |
| Use case | Reporting, analytics, summarized KPIs |
