DataGrid - Calculate & Generate Rank Column based on Entity Attribute Value - Highest to Lowest Value

Hi,

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).

Your inputs will be helpful.

Regards,
Lokesh

Hey,

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.

Best Regards
Daniel

Thank you for your reply.

Are there any examples in cuba with regards to pseudo columns?

Regards,
Lokesh

Have a look here:
https://doc.cuba-platform.com/manual-6.1/gui_Table.html

lower down in the Doc, you will see how generate and add a column, for your case maybe a Label containing the given Rank:

grafik

Daniel

Hi Daniel,

Thanks for your reply.

I checked and I am not sure if JPQL supports pseudo columns. I even checked the Manual. I did not find any information about pseudo columns.

Regards,
Lokesh

Hi all,

Still looking for a solution.

Your inputs will help.

Regards,
Lokesh

Hi,

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:

    @Transient
    @MetaProperty(related = "value")
    protected String rank;

Then the framework will sort by the value attribute when users click on the Rank column in DataGrid or Table components.

Hi,

Thanks for the information.

I have 1 more question.

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)

Thank You for help

Regards,
Lokesh

SQL wise you could achive this “Rank per department” with a

"dense_Rank() over (Partition by Department order by “your absolut rank”) "

…would be really interesting if someone knows how or has already done this with JPQL ?

Daniel

Some example will be helpful.

Thanks

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.

Hi,

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.

Suppose you have a Customer entity with name and value persistent attributes. First, add a non-persistent attribute rank:

public class Customer extends StandardEntity {

    @Column(name = "NAME")
    protected String name;

    @Column(name = "VALUE_")
    protected String value;

    @Transient
    @MetaProperty(related = "value")
    protected Integer rank;
//...

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:

<window xmlns="http://schemas.haulmont.com/cuba/window.xsd"
        ...
        dataSupplier="com.company.rankdemo.web.customer.CustomerDataSupplier">
    <dsContext>
        ...

If you need to update ranks after creating and editing records, add AfterCommitHandler’s to create/edit actions in the controller:

package com.company.rankdemo.web.customer;

import com.company.rankdemo.entity.Customer;
import com.haulmont.cuba.gui.components.AbstractLookup;
import com.haulmont.cuba.gui.components.actions.CreateAction;
import com.haulmont.cuba.gui.components.actions.EditAction;
import com.haulmont.cuba.gui.data.CollectionDatasource;

import javax.inject.Inject;
import javax.inject.Named;
import java.util.Map;
import java.util.UUID;

public class CustomerBrowse extends AbstractLookup {

    @Named("customersTable.create")
    private CreateAction customersTableCreate;
    @Named("customersTable.edit")
    private EditAction customersTableEdit;

    @Inject
    private CollectionDatasource<Customer, UUID> customersDs;

    @Override
    public void init(Map<String, Object> params) {
        customersTableCreate.setAfterCommitHandler(entity -> customersDs.refresh());
        customersTableEdit.setAfterCommitHandler(entity -> customersDs.refresh());
    }
}

That’s it. The data grid will display ranks calculated on the list loading.

See the project attached.
rank-demo.zip (82.4 KB)

Thanks for the sample solution.