A query that returns a list of defined queries and is stored in the database is referred to as a view. In addition, a query that is specified as a basic query will never, ever keep the data that is fetched throughout the operations.
A view has rows and columns since SQL views are similar to virtual tables. Views are created using fields from a single or several actual database tables.
You can include SQL statements and functions to a view to make it represent the data as if it were taken from a single table.
By selecting fields from one or more database tables, we can use SQL views to generate views. Additionally, since the data is retrieved through the built custom model, using the SQL view will result in reduced load. Additionally, arrange information in a way that feels natural or intuitive to consumers.
This blog will provide insight into the details of creating, updating, and deleting SQL views in Odoo 16.
CREATE VIEW
Following the CREATE VIEW command, we must now specify the view's name, and following the AS command, we must specify a SELECT query that describes the view. The select statement may also be from one or more tables, as shown below:
CREATE VIEW model_name.view_name [(List of columns)]
AS
Statement......
In order to update a view, a number of requirements must be met. We won't be able to update the view if any one of these prerequisites is not met.
The SELECT statement used to create the view shouldn't contain the GROUP BY or ORDER BY clauses. The keyword DISTINCT shouldn't be used in the SELECT query.
The View needs to include only NOT NULL values. Nested queries or complex queries shouldn't be utilized to build the view. A single table ought to be used to build the view. The view won't be able to be updated if several tables were utilized to create it.
UPDATE VIEW
To add or update fields from a view, use the CREATE OR REPLACE VIEW statement as shown below.
CREATE OR REPLACE VIEW name_of_view AS
SELECT column1,column2,....
FROM table
WHERE condition;
Let's now look at an example that illustrates how we can create the view for the project_id, user_id, and description from the model project.project. As shown below, we need to create a custom model.
class ProjectNote(models.Model):
"""Model for adding notes in projects"""
_name = 'project.note'
_description = 'Project Note'
_rec_name = 'projects_id'
projects_id = fields.Many2one('project.project',
string='Construction Project',
help='Specify the Projects')
user_id = fields.Many2one('res.users',
string='Responsible User',
help='Choose Responsible user')
description = fields.Html(string='Description',
help='Add more description')
Additionally, I included a menu option in the XML file for the view of the SQL query.
<menuitem id="job_note_" name="Job Notes"
action="job_note_action"
The action for the menu item was then added to the XML file as follows:
<!-- Form view of the model project.note -->
<record id="project_note_view_form" model="ir.ui.view">
<field name="name">project.note.view.form</field>
<field name="model">project.note</field>
<field name="arch" type="xml">
<form>
<sheet>
<group>
<field name="projects_id"/>
<field name="user_id"/>
</group>
<field name="description"/>
</sheet>
</form>
</field>
</record>
In order to access the fields I required through the SQL query, I now needed to add the tree view.
<!-- Tree view for the model project.note-->
<record id="project_note_view_tree" model="ir.ui.view">
<field name="name">project.note.view.tree</field>
<field name="model">project.note</field>
<field name="arch" type="xml">
<tree sample="1">
<field name="projects_id"/>
<field name="user_id"/>
</tree>
</field>
</record>
The created model was added as follows since the views were added after I needed to configure the SQL query in the specified view:
def init(self):
self._cr.execute("""
CREATE OR REPLACE VIEW project_note AS (
SELECT pn.project_id as project,
pn.user_id as user,
pn.description
FROM project_note pn
JOIN project_project pp ON pn.project_id = pp.id)
""")
DELETE VIEW
Although we know how to create views, what happens if one is no longer necessary? Naturally, we'll want to remove it. We can eliminate an existing View using SQL.
A View can be dropped or deleted with the DROP statement, as shown below.
DROP VIEW view_name;
Views give an additional layer of security and allow users to select information from various tables without having to know how to create a join. The number of columns in the view must match the number of columns specified in the select statement in order to rename columns without affecting modifications to the underlying tables.