show a column in a table that needs another entity/query

Support
#1

Hi Cuba,

I’m sorry to ask, maybe this question was asked many time, but I really don’t know where to start.
I spent hours reading the docs and the forum before writing, with no results, for an issue I would have solved in 10 minutes with a table component, in any “client” language… It is very frustrating!

The “problem” is simple:
I have a table “Bill”.
I have a table “Order”.
I have a table “Order Detail”.

I want a table like this one in the VIEW “Bills” and the user must have the option to create, update, remove a “Bill”

| Bill.number| Biil.amount | Order.number | Order.amount [TRANSIENT] (qty * price)

The relation are those.
An “Order” has many “Details” => Many “Details” one "Order"
An “Order” has many “Bills” => Many “Bills” one “Order”

To get the Order.amonut I have a SELECT Sum() that works like a charm.

the “Order” entity has already two COMPOSITION fields “OrderLine” and “Bill” that I use in the EDIT screen.

I’m scared to add an “Order” COMPOSITON field in the “Bills” table because it may create a cross reference that may be a problem, and it may not solve the problem since i want the SUM of all “OrderDETAILS.prices * qty”.

I’m convinced that the solution is simple but I didn’t know how AND where to fire the “Select SUM” query and how AND where to put the result in the table.

In case I need to have a separate view, the problem is that I still don’t know how to pass the “Order” parameter from the VIEW to the EDIT screen, and how to update correctly the “Order” lookup field in the edit screen…

Any help is REALLY appreciated
Thank you
Riccardo

#2

Hi Riccardo,

I think you could solve your task in CUBA the same way as in “any client language with a table component”. Just use the Table.addGeneratedColumn() to create your Order.amount column and do the calculations in the generateCell() method, returning Table.PlainTextCell object. You can use DataManager.loadValues() to execute your “select sum(*)” query.

But keep in mind that this approach has the following drawbacks:

  • It’s inefficient from the performance perspective - you run N+1 queries.
  • Sorting by the Order.amount column will work only within the current page.
  • Filtering by Order.amount is impossible because filter works on the database layer.

So I would strongly recommend having Order.amount as a persistent field in your database. You can maintain its value either when you work with order details in the Order editor screen (this is demonstrated in Part 4 of the Quick Start guide), or in an entity listener for the detail entity.

1 Like
#3

Hi Konstantine,
thank you for the quick reply.

I took your suggestion about having Order.amount as persistent field. It was in effect very easy to implement since i had already calculated it in the editor screen.

I was unaware of the DataManager.loadValues() thank you