In some cases, you might need to display data in Odoo that comes from multiple tables or complex joins without storing it in a traditional Odoo model. For this purpose, Odoo provides the _auto = False attribute for models, which allows you to define a model without automatically creating a database table. Instead, you can link the model to a SQL View.
This article will guide you through creating a custom view model that aggregates data from multiple tables using a SQL query.
Example: Aggregating Leave & Outing Requests
We want to display a combined list of leave requests and outing requests from two different tables:
hr_outing_request_form
hr_leave_request_form
request_history.py
from odoo import models, fields, api, tools
from odoo.exceptions import UserError,ValidationError
from datetime import date
from dateutil.relativedelta import relativedelta
import logging
_logger = logging.getLogger(__name__)
import json
from datetime import datetime, timedelta,time
from pytz import timezone, utc
import json
from .. import defs
class RequestUnion(models.Model):
_name = "hr.request.union"
_auto = False
_order = "request_date desc"
name = fields.Char("Application Code")
employee_id = fields.Many2one('hr.employee', "Employee")
employee_name = fields.Char("Employee Name")
source_id = fields.Integer("ID")
leave_kind_id = fields.Many2one('hr.outing.request.leave_kind', string="Leave Kind")
request_date = fields.Date("Request Date")
state = fields.Selection(defs.LeaveRequestStatus.attrs.items(), default=defs.LeaveRequestStatus.draft, string="Status", tracking=True)
state_name = fields.Char("State")
employee_company_status = fields.Selection(
selection=defs.EmployeeBranchStatus.attrs.items(),
string="Company"
)
leave_request_start = fields.Date("Start Date")
leave_request_end = fields.Date("End Date")
type = fields.Selection([('outing', 'Outing'), ('leave', 'Leave')], string="Request Type")
@api.model
def init(self):
tools.drop_view_if_exists(self._cr, 'hr_request_union')
self.env.cr.execute("""
CREATE VIEW hr_request_union AS (
WITH base_max AS (
SELECT COALESCE(MAX(id), 0) AS max_id FROM hr_outing_request_form
),
outing AS (
SELECT
ROW_NUMBER() OVER() AS rownum,
id AS source_id, 'hr.outing.request.form' AS source_model,
code AS name, employee_id, create_date as request_date, leave_kind_id,
employee_company_status, state, state_name, employee_name,
leave_request_start, leave_request_end, 'outing' AS type
FROM hr_outing_request_form
WHERE employee_id IS NOT NULL
),
leave AS (
SELECT
b.max_id + ROW_NUMBER() OVER() AS rownum,
f.id AS source_id, 'hr.leave.request.form' AS source_model,
f.code AS name, f.employee_id, f.create_date as request_date, f.leave_kind_id,
f.employee_company_status, f.state, f.state_name, f.employee_name,
f.leave_request_start, f.leave_request_end, 'leave' AS type
FROM hr_leave_request_form f, base_max b
WHERE f.employee_id IS NOT NULL
)
SELECT
o.rownum AS id, o.*
FROM outing o
UNION ALL
SELECT
l.rownum AS id, l.*
FROM leave l
);
""")
def unlink(self):
for record in self:
if record.type == "outing":
self.env.cr.execute("DELETE FROM hr_outing_request_form WHERE id = %s", (record.source_id,))
elif record.type == "leave":
self.env.cr.execute("DELETE FROM hr_leave_request_form WHERE id = %s", (record.source_id,))
return True
request_history.xml
<odoo>
<record id="view_action_request_union_search" model="ir.ui.view">
<field name="name">view_action_request_union_search</field>
<field name="model">hr.request.union</field>
<field name="arch" type="xml">
<search>
<field name="name" />
<field name="employee_name" />
<separator/>
<searchpanel>
<field name="type" enable_counters="1" />
<field name='leave_kind_id' />
<!-- <field name="request_date" /> -->
<field name='employee_company_status' enable_counters="1" />
<field name="state" enable_counters="1" />
</searchpanel>
</search>
</field>
</record>
<record id="view_hr_request_union_form" model="ir.ui.view">
<field name="name">hr.request.union.form</field>
<field name="model">hr.request.union</field>
<field name="arch" type="xml">
<form string="Outing/Leave Request" create="false" edit="false" >
<sheet>
<group>
<group>
<field name="name"/>
<field name="employee_id"/>
<field name="employee_name" readonly="1"/>
<field name="request_date"/>
<field name="type"/>
<field name="leave_kind_id"/>
</group>
<group>
<field name="leave_request_start"/>
<field name="leave_request_end"/>
<field name="state"/>
<field name="state_name" readonly="1"/>
<field name="employee_company_status" readonly="1"/>
</group>
</group>
</sheet>
</form>
</field>
</record>
<record id="view_action_request_union_tree" model="ir.ui.view">
<field name="name">view_action_request_union_tree</field>
<field name="model">hr.request.union</field>
<field name="arch" type="xml">
<tree string="Leave Requests" delete="1" create="0" edit="0" >
<field name="id"/>
<field name="name"/>
<field name='leave_kind_id' />
<field name='employee_company_status' />
<field name="employee_name"/>
<field name='request_date' />
<field name='leave_request_start' />
<field name='leave_request_end' />
<field name='state' column_invisible="1" />
<field name='state_name' decoration-info="state == 'submitted' " decoration-success="state == 'approved' " decoration-danger="state == 'rejected'" widget="badge" />
</tree>
</field>
</record>
<record id="action_request_union" model="ir.actions.act_window">
<field name="name">Request History</field>
<field name="res_model">hr.request.union</field>
<field name="view_mode">tree,form</field>
<field name="context">{}</field>
</record>
</odoo>
Reply