Define aggregated model#
Aggregated data are useful to analyze business.
Tryton can provide such data using ModelSQL
class which
are not based on an existing table in the database but using a SQL query.
This is done by defining a table_query()
method
which returns a SQL FromItem
.
Let’s create a ModelSQL
which aggregate the number of
opportunity converted or lost per month.
First we create a ModelSQL
class which defines a
table_query()
in opportunity.py
:
from sql import Literal
from sql.aggregate import Count, Min
from sql.functions import CurrentTimestamp, DateTrunc
...
class OpportunityMonthly(ModelSQL, ModelView):
"Opportunity Monthly"
__name__ = 'training.opportunity.monthly'
month = fields.Date("Month")
converted = fields.Integer("Converted")
lost = fields.Integer("Lost")
@classmethod
def table_query(cls):
pool = Pool()
Opportunity = pool.get('training.opportunity')
opportunity = Opportunity.__table__()
month = cls.month.sql_cast(
DateTrunc('month', opportunity.end_date))
query = opportunity.select(
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
Min(opportunity.id).as_('id'),
month.as_('month'),
Count(
Literal('*'),
filter_=opportunity.state == 'converted').as_('converted'),
Count(
Literal('*'),
filter_=opportunity.state == 'lost').as_('lost'),
where=opportunity.state.in_(['converted', 'lost']),
group_by=[month])
return query
Note
The table query must return a value for all the fields of the model but also
a unique id
and a value for the create and write fields.
Note
We get the SQL table from the __table__()
method.
Note
We use sql_cast()
to convert the timestamp
returned by date_trunc
into a date
.
Then as usual we register the ModelSQL
class in the in
the Pool
as type model
in __init__.py
:
def register():
...
Pool.register(
...,
opportunity.OpportunityMonthly,
module='opportunity', type_='model')
And to display we create a list view and the menu entry in
opportunity.xml
:
<tryton>
<data>
...
<record model="ir.ui.view" id="opportunity_monthly_view_list">
<field name="model">training.opportunity.monthly</field>
<field name="type">tree</field>
<field name="name">opportunity_monthly_list</field>
</record>
<record model="ir.action.act_window" id="act_opportunity_monthly_form">
<field name="name">Monthly Opportunities</field>
<field name="res_model">training.opportunity.monthly</field>
</record>
<record model="ir.action.act_window.view" id="act_opportunity_monthly_form_view">
<field name="sequence" eval="10"/>
<field name="view" ref="opportunity_monthly_view_list"/>
<field name="act_window" ref="act_opportunity_monthly_form"/>
</record>
<menuitem
parent="menu_opportunity"
action="act_opportunity_monthly_form"
sequence="50"
id="menu_opportunity_monthly_form"/>
</data>
</tryton>
And now the view in view/opportunity_monthly_list.xml
:
<tree>
<field name="month"/>
<field name="converted"/>
<field name="lost"/>
</tree>
Update database#
As we have registered new model and XML records, we need to update the database with:
$ trytond-admin -d test --all
And restart the server and reconnect with the client to test computing aggregate:
$ trytond
Note
As you can see the model behaves like the other models, except that you can not create, delete nor write on them.
This is all for your first module. If you want to learn more about Tryton, you can continue on specific topics.