Enable Dark Mode!
how-to-create-sql-view-odoo.jpg
By: Irfan A

How to Create SQL View in Odoo?

Technical

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.


If you need any assistance in odoo, we are online, please chat with us.



0
Comments



Leave a comment

 


whatsapp
location

Calicut

Cybrosys Technologies Pvt. Ltd.
Neospace, Kinfra Techno Park
Kakkancherry, Calicut
Kerala, India - 673635

location

Kochi

Cybrosys Technologies Pvt. Ltd.
1st Floor, Thapasya Building,
Infopark, Kakkanad,
Kochi, India - 682030.

location

Bangalore

Cybrosys Techno Solutions
The Estate, 8th Floor,
Dickenson Road,
Bangalore, India - 560042

Send Us A Message