A query that is stored in the database and returns a set of defined queries can be coined as a view. In addition, in a query which is defined a simple query never and ever will store the data that is fetched in the operations.
This blog will provide an insight into the aspects on how to create SQL views in Odoo
Views in the SQL are like virtual tables; therefore, by using sql view we can create a view by selecting fields from one or more tables that are present in the database. Moreover, by using the SQL view the load will be reduced since the data is accessed through the custom model that is created. Furthermore, structure data in a way that users find natural or intuitive.
Now to run the operation first, we need to specify the name of the view after the CREATE VIEW command and specify a SELECT statement that defines the view after the AS command. Additionally, the select statement can be from one or more tables as mentioned below:
CREATE VIEW model_name.view_name [(List of columns)]
AS
Statement you can given here
Furthermore, the select statement is the one which is used to create the view therefore, we cannot include ORDER BY and GROUP BY clause. In addition, for adding or removing fields from a view we can use create and replace view statement as described below:
CREATE OR REPLACE VIEW name_of_view AS
SELECT c1,c2,....
FROM name_of_table
WHERE your_condition;
Now let's look at an example that shows how we can make the view for the customer, date deadline, project name from the project.task . For the initially we need to create a custom model as shown below:
class ProjectTaskDetails(models.Model):
_name = "project.task.details"
_auto = False
project = fields.Many2one('project.project')
partner = fields.Many2one('res.partner', string='Customer')
date_deadline = fields.Char(string='Deadline Date')
Here, I added three fields as I need these three details separately from the project.task model and also provided _auto = false.
Further, I added the menu item in the xml file for the view of the sql query.
<menuitem name="Project Task Details" action="action_project_task_details_view id="menu_project_task_details_action" parent="project.menu_project_config" sequence="4"/>
Then I added the action in the xml file for the menu item:
<!-- Form view Action for Project Task Details ?
<record id="action_project_task details view" model="ir.actions.act_window">
<field name="name">Project task</field>
<field name="type">ir.actions.act_window</field>
<field name="res_model">project.task.details</field>
<field name="view_mode">tree,form</field>
<field name="view_id" eval="False"/>
</record>
Now I needed to add the tree view, to view the fields that I need through the sql query:
<record model="ir.ui.view" id="action_project_task_details_tree">
<field name="name">Project Task Details</field>
<field name="model">project.task.details</field>
<field name="arch" type="xml">
<tree string="Project Task" create="false">
<field name='project'/>
<field name='partner'/>
<field name='date_deadline'/>
</tree>
</field>
</record>
As the views are being added next I need to set the sql query to be configured in the given view therefore, I added the created model as shown below:
def init(self):
self._cr.execute("""
CREATE OR REPLACE VIEW project_task_details AS (
SELECT row_number() OVER () as id,
ps.project_id as project,
ps.date_deadline as date_deadline,
aa.partner_id as partner
FROM project_task ps
LEFT JOIN project_project pp ON ps.project_id = pp.id
LEFT JOIN account_analytic_account aa ON pp.analytic_account_id = aa.id )
""")
In the query that I configured the data taken from the project. task is set in the custom model therefore, I can access data from the custom view. Moreover, I can define what I should view in the custom model.