Build Live SQL Views
First, Odoo Dynamic View allows developers to merge data from multiple Odoo models into a single real-time view. Next, this dynamic SQL view relies on PostgreSQL to deliver up-to-date information without redundancy. Then, we guide you through every step for creating and deploying an Odoo Dynamic View in your custom module. Moreover, you can optimize performance and maintain data integrity with this approach. Additionally, you can find more details in the official Odoo ORM documentation: https://www.odoo.com/documentation/15.0/developer/reference/addons/orm.html.
Understanding Odoo Dynamic View Basics
First, we define what an Odoo Dynamic View is and why it matters. Next, we contrast it with a standard Odoo model that creates its own database table. Then, we show that Odoo Dynamic View uses the _auto = False flag to avoid table creation. Moreover, we explain that you implement the view in the model’s init() method using PostgreSQL’s CREATE OR REPLACE VIEW command. Additionally, we note that this approach keeps your data normalized and fresh, because PostgreSQL generates results on demand. Finally, we link to PostgreSQL’s CREATE VIEW docs for deeper reading: https://www.postgresql.org/docs/current/sql-createview.html.
What Is a Live SQL View?
First, a live SQL view acts like a virtual table that executes a SELECT query on demand. Next, it draws rows from one or more tables at query time. Then, it never stores data physically beyond its base tables. Moreover, it ensures that any change in the source tables appears instantly in the view. Additionally, it enforces data consistency across your reports without manual refresh.
Why Choose Odoo Dynamic View?
First, you avoid maintaining duplicate fields or tables. Next, you leverage PostgreSQL’s query planner for optimized joins. Then, you reduce module upgrade complexity by moving logic to SQL. Moreover, you enable advanced reporting by exposing computed fields in a tree or form view. Additionally, you combine data from diverse models without writing heavy Python loops.
Advantages of Odoo Dynamic View
First, Odoo Dynamic View delivers real-time data updates. Next, it prevents data duplication by pulling live records. Then, it reduces Python overhead because PostgreSQL handles joins and filters. Moreover, it improves performance for complex reports. Additionally, it offers a clean separation between data storage and presentation logic.
Real-Time Insights
First, you get immediate feedback when source records change. Next, you avoid stale caches or delayed cron jobs. Then, you display up-to-the-second KPIs in dashboards. Moreover, you maintain trust in your reports because they reflect the current database state.
Better Maintainability
First, you centralize logic in the SQL view definition. Next, you avoid scattered Python scripts that seed or migrate data. Then, you simplify future changes by editing one SQL query. Moreover, you reduce risk of mismatched updates across tables.
Prerequisites for Creating an Odoo Dynamic View
First, you install Odoo (v14+ recommended) and PostgreSQL. Next, you grant your Odoo user rights to create views in the database. Then, you ensure basic familiarity with Python, XML, and SQL. Moreover, you set up a custom module scaffold. Additionally, you verify that your module depends on any models you join in the view.
Environment Setup
First, you create a new addon folder under addons/. Next, you include an __init__.py and a __manifest__.py. Then, you install any required community modules. Moreover, you start your Odoo server in developer mode. Additionally, you open logs to spot SQL errors early.
Database Permissions
First, you confirm that the Odoo database user has CREATE and DROP privileges. Next, you test view creation manually in psql. Then, you note any permission failures and adjust roles. Moreover, you restart Odoo to pick up permission changes.
Module Structure for Odoo Dynamic View
First, we outline a typical file layout for a dynamic view module:
my_dynamic_view/
├── __init__.py
├── __manifest__.py
├── models/
│ ├── __init__.py
│ └── student_school_dynamic_view.py
└── views/
└── student_school_dynamic_view_views.xmlNext, we explain each file’s role. Then, we point out that you place Python code under models/ and XML under views/. Moreover, we mention that you import your Python file in models/__init__.py. Additionally, we note that you reference the XML in the manifest under the data key.
init.py
First, your root __init__.py imports the models package. Next, you write:
# __init__.py
from . import modelsmodels/init.py
First, you import your dynamic view file. Next, you write:
# models/__init__.py
from . import student_school_dynamic_viewmanifest.py
First, you declare module metadata and dependencies. Next, you include your XML under data. Then, you ensure proper versioning. Moreover, you set installable: True. Additionally, you describe your module’s summary.
# __manifest__.py
{
'name': 'Student School Dynamic View',
'version': '1.0',
'author': 'Your Name',
'category': 'Education',
'summary': 'Odoo Dynamic View for combined student-school data',
'depends': ['base', 'school_management'],
'data': [
'views/student_school_dynamic_view_views.xml',
],
'installable': True,
'application': False,
}Defining the Python Model for Odoo Dynamic View
First, we write the Python class that defines our dynamic view. Next, we set _auto = False to prevent table creation. Then, we implement an init() method that drops and recreates the SQL view. Moreover, we expose fields that map directly to the SQL query’s columns. Additionally, we include detailed comments to clarify each step.
student_school_dynamic_view.py
# models/student_school_dynamic_view.py
from odoo import models, fields, tools
class StudentSchoolDynamicView(models.Model):
_name = 'student.school.dynamic.view'
_description = 'Combined Student and School Data'
_auto = False # Skip table creation, use SQL view instead
school_name = fields.Char(string='School Name')
school_phone = fields.Char(string='Phone')
school_email = fields.Char(string='Email')
school_type = fields.Selection([
('private', 'Private School'),
('public', 'Public School'),
], string='Type')
student_name = fields.Char(string='Student Name')
student_rno = fields.Char(string='Roll Number')
student_fees = fields.Float(string='Fees')
student_seq = fields.Integer(string='Sequence')
def init(self):
"""Create or replace the SQL view for dynamic data."""
# Drop existing SQL view if present
tools.drop_view_if_exists(self.env.cr, self._table)
# Build the SQL query for the view
query = f"""
CREATE OR REPLACE VIEW {self._table} AS (
SELECT
std.id AS id,
std.roll_number AS student_rno,
std.name AS student_name,
std.student_fees AS student_fees,
std.student_seq AS student_seq,
sp.name AS school_name,
sp.phone AS school_phone,
sp.email AS school_email,
sp.school_type AS school_type
FROM school_student AS std
JOIN school_profile AS sp
ON std.school_st_id = sp.id
)
"""
# Execute the SQL to register the view
self.env.cr.execute(query)First, we import models, fields, and tools from Odoo. Next, we define the model and disable _auto. Then, we list each field to match SQL columns. Moreover, we implement init() that drops and recreates the view. Finally, we execute the SQL query via the database cursor.
Setting Up the XML View Definition
First, we expose our dynamic model in the UI via an XML tree view. Next, we reference the model name and list fields in the desired order. Then, we add a menu item and an action for easy access. Moreover, we ensure that users can search and filter on any field. Additionally, we include a record for the ir.ui.menu entry.
student_school_dynamic_view_views.xml
<!-- views/student_school_dynamic_view_views.xml -->
<odoo>
<!-- Tree view for dynamic data -->
<record id="view_student_school_dynamic_tree" model="ir.ui.view">
<field name="name">student.school.dynamic.tree</field>
<field name="model">student.school.dynamic.view</field>
<field name="arch" type="xml">
<tree string="Student & School Data">
<field name="student_seq"/>
<field name="school_name"/>
<field name="school_phone"/>
<field name="school_email"/>
<field name="school_type"/>
<field name="student_name"/>
<field name="student_rno"/>
<field name="student_fees"/>
</tree>
</field>
</record>
<!-- Action to open the tree view -->
<record id="action_student_school_dynamic" model="ir.actions.act_window">
<field name="name">Student-School Report</field>
<field name="res_model">student.school.dynamic.view</field>
<field name="view_mode">tree</field>
</record>
<!-- Menu item under Reporting -->
<menuitem id="menu_student_school_dynamic"
name="Student-School Report"
parent="school_management.menu_reporting"
action="action_student_school_dynamic"/>
</odoo>First, we define the tree view with all fields. Next, we create an action to launch the view. Then, we add a menu entry under the existing reporting menu. Moreover, we ensure string labels read clearly. Finally, we validate the XML with Odoo’s schema.
Configuring the Manifest for Odoo Dynamic View
First, we update __manifest__.py to include our XML. Next, we declare dependencies on modules that supply base models (e.g., school_management). Then, we list views/student_school_dynamic_view_views.xml under data. Moreover, we set installable to True. Additionally, we provide a concise summary and description for SEO.
# __manifest__.py
{
'name': 'Student School Dynamic View',
'version': '1.0',
'summary': 'Odoo Dynamic View for combined student and school data',
'description': 'This module creates an Odoo Dynamic View that combines student and school info into a live SQL view.',
'author': 'Your Name',
'category': 'Education',
'depends': ['base', 'school_management'],
'data': [
'views/student_school_dynamic_view_views.xml',
],
'installable': True,
'application': False,
}First, we include the view XML under data. Next, we confirm the dependency modules. Then, we save changes and restart Odoo. Moreover, we install the module via Apps. Finally, we verify that the dynamic view appears in the menu.
Testing Your Odoo Dynamic View
First, we perform manual tests to confirm that the view shows correct data. Next, we navigate to the “Student-School Report” menu. Then, we verify that all fields display and match underlying tables. Moreover, we create new school_profile and school_student records to test real-time updates. Additionally, we ensure that filters and sorting work as expected.
Automated Test with TransactionCase
First, we write a Python test to install the module and check the view. Next, we create test records and run a SELECT query on the SQL view. Then, we assert that the view returns the expected number of rows. Moreover, we incorporate this test into our CI pipeline.
# tests/test_dynamic_view.py
from odoo.tests.common import TransactionCase
from odoo import tools
class TestStudentSchoolDynamicView(TransactionCase):
def setUp(self):
super().setUp()
self.school = self.env['school_profile'].create({
'name': 'Test School',
'phone': '1234567890',
'email': 'info@test.edu',
'school_type': 'public',
})
self.student = self.env['school_student'].create({
'name': 'Test Student',
'roll_number': 'R001',
'student_fees': 100.0,
'student_seq': 1,
'school_st_id': self.school.id,
})
def test_view_content(self):
result = self.env.cr.execute(f"SELECT * FROM {self.env['student.school.dynamic.view']._table}")
rows = self.env.cr.fetchall()
assert len(rows) == 1
assert rows[0][2] == 'Test Student'First, we import TransactionCase and tools. Next, we create a school and student record. Then, we query the SQL view directly. Moreover, we assert correct row count and content. Finally, we run tests with:
# run pytest for automated validation
$ pytest --maxfail=1 --disable-warnings -qTroubleshooting Odoo Dynamic View Errors
First, we identify common SQL syntax mistakes. Next, we check that table names match the database schema. Then, we verify that the view’s id column aligns with model requirements. Moreover, we inspect Odoo logs for cursor errors like “relation does not exist.” Additionally, we open a psql shell to test the view query manually.
Common Error: Missing Column
First, an “undefined column” error occurs when you rename a field. Next, you update your SQL query to include the new column name. Then, you drop and recreate the view with the adjusted init() method. Moreover, you restart Odoo to refresh the cache.
Common Error: Permission Denied
First, you get “permission denied for relation…” when your DB user lacks privileges. Next, you grant SELECT on underlying tables:
GRANT SELECT ON school_profile TO odoo;
GRANT SELECT ON school_student TO odoo;Then, you rerun the module install. Moreover, you confirm that Odoo can now drop and create views.
Best Practices for Odoo Dynamic View
First, keep your SQL queries simple to aid readability. Next, avoid heavy computations in the view; offload them to reports or cron jobs. Then, document your SQL logic with comments inside the init() method. Moreover, version your view by tracking changes in your module’s changelog. Additionally, test your view on large datasets to catch performance issues early.
Performance Tips
First, index joined columns in underlying tables. Next, limit the view to necessary records using WHERE clauses. Then, use materialized views for extremely large datasets with periodic refresh. Moreover, choose read-only access when appropriate.
Security Tips
First, abide by Odoo’s record rules; avoid exposing sensitive fields. Next, use ir.rule definitions to restrict dynamic view records. Then, do not embed user input directly into SQL; use parameterized queries when needed.
Advanced Topics for Odoo Dynamic View
First, you can create parameterized dynamic views that accept filters via context or fields. Next, you can combine dynamic views with scheduled actions to prepopulate materialized views. Then, you can extend the view at runtime by registering new SQL snippets in other modules. Moreover, you can integrate dynamic views with BI tools by exposing them as read-only models. Additionally, you can publish Odoo Dynamic Views via REST APIs for external dashboards.
Conclusion
First, we covered how to build an Odoo Dynamic View by defining a Python model with _auto = False and an init() hook. Next, we exposed the view via XML, updated the manifest, and tested manually and automatically. Then, we discussed troubleshooting, best practices, and advanced uses. Moreover, you now understand how to deliver real-time, normalized reports without data duplication. Finally, you can apply this pattern to any Odoo module to enhance reporting and performance.
Discover more from teguhteja.id
Subscribe to get the latest posts sent to your email.