group by tree with table query

[Roei Erez]

Hi,

We have a system with tests, each Test has a many2one field of User who is its executor and a time that the test execution took.
We would like to show a tree view that shows average time per test and when click on a test it is like a pivot table that opens the users and show the total per user.


Name | Average Time

+Test1 | 2 hours
User1 1 hours
User2 | 1 hours


We saw such tree views in the client but we would like to implement that without inserting real rows into the database for this tree view but rather query the information from the existing Test model table (using table query maybe?)

Appreciate if someone can help here, or refer me to an example of such requirements.
Thanks.

[Sergi Almacellas Abellana]

On 6 de gener de 2018 2.06.53 CET, Roei Erez <roei...@gmail.com> wrote:
>Hi,
>
>We have a system with tests, each Test has a many2one field of User who
>is its executor and a time that the test execution took.
>We would like to show a tree view that shows average time per test and
>when click on a test it is like a pivot table that opens the users and
>show the total per user.
>
>
>Name | Average Time
>
>+Test1 | 2 hours
> User1 1 hours
> User2 | 1 hours
>
>
>We saw such tree views in the client but we would like to implement
>that without inserting real rows into the database for this tree view
>but rather query the information from the existing Test model table
>(using table query maybe?)

Yes you should use a new model which defines the table_query method. This method must return a python-sql query with the computed data. In adition to the displayed data you must compute the id, create_uid, create_date, write_uid and write_date.

In order to open the records detail you can use the tree_open keyword which will trigger a new action. This action will recieve the id of the clicked record on the active_id keyword. So it mus have a domain like:

[('some_field', '=', Eval('active_id'))]

In order to show only the related records on the target model.

>
>Appreciate if someone can help here, or refer me to an example of such
>requirements.
>Thanks.

--
Enviado desde mi dispositivo Android con K-9 Mail. Por favor, disculpa mi brevedad.

[Roei Erez]

Hi Sergi,

Appreciate your response and help.
I have a few more questions on this:
1. Computing the ids: since this is a custom model for analytics and the records don't really exist, so do I have to create some "fake" ids?
2. I was looking here: http://demo4.4.tryton.org/ and what I need is the Financial/Reporting/Balance Sheet report. To get like a grouped time per test split by users.

I looked at the source for this but it looks like they have actual records in the database and I am looking to do it with table query without the need to insert a record per test per user just for analytics.
Deeper looking I saw that the way to tell the client that it is an expanded table is to use the field_childs on the treeview and then returns the child ids in that field. The problem is that I don't have real records with ids, so I wonder if there is and sample that I can look at.

Thanks,
Roei.

[Sergi Almacellas Abellana]

El 06/01/18 a les 12:59, Roei Erez ha escrit:

> Hi Sergi,
>
> Appreciate your response and help.
> I have a few more questions on this:
> 1. Computing the ids: since this is a custom model for analytics and the records don't really exist, so do I have to create some "fake" ids?

Yes you have to generate the ids. Since you are grouping data, you can
generate the id by combining the ids of the grouped records. Here you
have some examples:

http://hg.tryton.org/modules/sale_opportunity/file/e18efbf79a26/opportunity.py#l678

> 2. I was looking here: http://demo4.4.tryton.org/ and what I need is the Financial/Reporting/Balance Sheet report. To get like a grouped time per test split by users.
>
> I looked at the source for this but it looks like they have actual records in the database and I am looking to do it with table query without the need to insert a record per test per user just for analytics.
> Deeper looking I saw that the way to tell the client that it is an expanded table is to use the field_childs on the treeview and then returns the child ids in that field. The problem is that I don't have real records with ids, so I wonder if there is and sample that I can look at.

Tryton is based on ids, so you you have to generate the records with ids.



--
Sergi Almacellas Abellana
www.koolpi.com
Twitter: @pokoli_srk