I have a Collection Datasource (Entity) - Audit Object.
The entity has attributes : Name, Number, Value
I need to calculate Rank sequence based on “Value” attribute. The object with the highest value is given Rank 1, and so on.
I need to display list of Objects from highest “Value” to Lowest value with the corresponding Rank in a sequential order in a new “generated column”.
I an not sure if I want to use Rank as “Non-persistent entity” or “hard coded value” in a column as the Object’s “Value” attribute can be edited and the changes should reflect immediately on the Grid (no recalculation every time changes are made).
for displaying the “audit Object” items, in a descending “Value” order, i would just sort the data in the given query and “order by x.value desc” should do the job.
this way you get the items in a descending order based on the value.
regarding the rank: i’m not sure if you can use some pseudo column like “rownum” in a JPQL query. maybe someone has already did something like this already. this would be the best way, generating this ranking column via the query.
of course, you could create the rank column programatically when loading the given Screen/Table, which displays the “audit object” items. but i think the best way is, to solve this via the query.
Of course you cannot use “pseudo columns” (we usually call them “non-persistent attributes”) in JPQL, because they are not mapped to table columns and ORM doesn’t recognize them at all.
But you can create a non-persistent (see Transient checkbox in the Studio Create attribute dialog) attribute and link it to a normal attribute using the related annotation property, for example:
Lets say the Entity has another attribute called “Department”
How can I Rank the List of Audit Objects Based on Department?
For Example : I have 5 Audit Objects
Name Number Value Department
aaa 123 10 IT
bbb 223 8 HR
ccc 334 12 IT
ddd 453 6 HR
eee 565 7 IT
I would like the Datagrid to be displayed with Rank and Department Rank as Generated Column.
Name Number Value Department Rank Department Rank
ccc 334 12 IT 1 1
aaa 123 10 IT 2 2
bbb 223 8 HR 3 1
eee 565 7 IT 4 3
ddd 453 6 HR 5 2
The above Table is what I want to achieve. I would like to generate rank as a sequence based on “Value”. And I would like like to generate Department Rank based on “Department and Value”.
Do you know a way this can be achieved?
I have attached a file where u can view the table more accurately.DataGrid.pdf (23.2 KB)
Do you need paging for this DataGrid or the total number of rows is not large and it is acceptable to load them all at once?
I’m asking because if the number of rows is large (e.g. >1000), recalculating ranks on each load will be too costly and it would be better to crate rank columns in the database and calculate them on update in entity listeners. Then loading and displaying ranks would be straightforward.
The total number of rows is not large. Paging is not needed. The datagrid is only for display. We will not perform any operations on the datagrid(CRUD). Just loading the grid for user to see the list of Audit Objects with Rank by Value and Department Rank by (Department id and Value). They can be loaded all at once.
The grid needs to look like the one in the pdf.DataGrid.pdf (23.2 KB)
Thanks for the help. I am really stuck with the ranking by department.
Then create a class extending GenericDataSupplier in the package of your screen. That class will intercept data loading and fill the rank attribute based on value:
package com.company.rankdemo.web.customer;
import com.company.rankdemo.entity.Customer;
import com.haulmont.cuba.core.entity.Entity;
import com.haulmont.cuba.core.global.LoadContext;
import com.haulmont.cuba.gui.data.impl.GenericDataSupplier;
import javax.annotation.Nonnull;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
public class CustomerDataSupplier extends GenericDataSupplier {
@Nonnull
@Override
public <E extends Entity> List<E> loadList(LoadContext<E> context) {
List<E> list = super.loadList(context);
List<Customer> customers = new ArrayList(list);
customers.sort(Comparator.comparing(Customer::getValue));
int i = 1;
for (Customer customer : customers) {
customer.setRank(i++);
}
return list;
}
}
Register this class in the dataSupplier attribute of your screen descriptor:
