Hello everyone,
I have a question regarding a solution I’ve been looking for to address my issue.
I have a list of items, automatically generated by Jmix, to display certificate values.
I have created a table or card at the top of the grid listing screen, which shows the number of records per status. These values are obtained from a JPQL query that performs a COUNT and GROUP BY on the entire table (as you’ll see in the code I’ll provide).
My issue is that I would like the displayed counts to change whenever I apply a filter to the grid, such as a date range or a filter on a specific field. When the filter is applied, my counter list should be updated according to the newly filtered dataset.
I’ve searched the documentation and couldn’t find a way to do this. I saw that I have access to the conditions used in each filtered query, but I can’t use them in my new JPQL query.
It’s also not feasible to retrieve the entire filtered list and then iterate over it to count the values, as it takes too long—I have over 500,000 records.
What options do I have to achieve what I need? I want to update my counters based on the filter used by the user on the screen, rather than just displaying the total count of the entire table.
I really appreciate any help.
Best regards!
P.S.: I am providing my code, including the controller and the view. I’m using Jmix version 2.5.0.
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<view xmlns="http://jmix.io/schema/flowui/view"
title="msg://certificateListView.title"
focusComponent="certificatesDataGrid">
<data>
<collection id="certificatesDc"
class="py.com.itti.secure.certificates.backoffice.entity.Certificate">
<fetchPlan extends="_base">
<property name="pk"/>
</fetchPlan>
<loader id="certificatesDl" readOnly="true">
<query>
<![CDATA[select e from Certificate e]]>
</query>
</loader>
</collection>
</data>
<facets>
<dataLoadCoordinator auto="true"/>
<urlQueryParameters>
<genericFilter component="genericFilter"/>
<pagination component="pagination"/>
</urlQueryParameters>
</facets>
<actions>
<action id="selectAction" type="lookup_select"/>
<action id="discardAction" type="lookup_discard"/>
</actions>
<layout>
<genericFilter id="genericFilter"
dataLoader="certificatesDl">
<properties include=".*"/>
</genericFilter>
<hbox id="buttonsPanel" classNames="buttons-panel">
<button id="createButton" action="certificatesDataGrid.create"/>
<button id="editButton" action="certificatesDataGrid.edit"/>
<button id="refreshButton" action="certificatesDataGrid.refresh"/>
<button id="removeButton" action="certificatesDataGrid.remove"/>
<button id="excelExportBtn" action="certificatesDataGrid.excelExport"/>
</hbox>
<fragment id="certificatesSummaryFragment"
class="py.com.itti.secure.certificates.backoffice.view.fragments.CertificatesSummaryFragment"/>
<dataGrid id="certificatesDataGrid"
width="100%"
minHeight="20em"
dataContainer="certificatesDc"
selectionMode="MULTI"
multiSort="true" detailsVisibleOnClick="true">
<actions>
<action id="create" type="list_create" visible="false" enabled="false"/>
<action id="edit" type="list_edit" visible="false" enabled="false"/>
<action id="remove" type="list_remove" visible="false" enabled="false"/>
<action id="refresh" type="list_refresh"/>
<action id="excelExport" type="grdexp_excelExport" icon="FILE_TABLE">
<properties>
<property name="columnsToExport" value="ALL_COLUMNS"/>
</properties>
</action>
</actions>
<columns resizable="true" sortable="true">
<column property="id" autoWidth="true" filterable="true" frozen="true"/>
<column property="signer" autoWidth="true" filterable="true"/>
<column property="provider" autoWidth="true" filterable="true"/>
<column property="status" autoWidth="true" filterable="true"/>
<column property="providerSign" autoWidth="true" filterable="true"/>
<column property="createdAt" autoWidth="true" filterable="true" textAlign="CENTER"/>
<column property="updatedAt" autoWidth="true" filterable="true" textAlign="CENTER"/>
<column property="requestedBy" autoWidth="true" filterable="true"/>
<column property="isBatch" autoWidth="true" filterable="true" textAlign="CENTER"/>
<column property="validityStartDate" autoWidth="true" filterable="true"/>
<column property="validityEndDate" autoWidth="true" filterable="true"/>
<column property="order" autoWidth="true" filterable="true"/>
<column property="product" autoWidth="true" filterable="true"/>
<column property="pk" visible="false" filterable="true"/>
<column property="title" visible="false" filterable="true"/>
<column property="signerDocumentType" visible="false"/>
<column property="signerPhoneNumber" visible="false"/>
<column property="serialNumber" visible="false"/>
<column property="hasTemporalPin" visible="false"/>
<column property="expirationDate" visible="false"/>
<column key="actions" filterable="false" sortable="false" autoWidth="true" textAlign="CENTER"/>
</columns>
</dataGrid>
<hbox id="lookupActions" visible="false">
<button id="selectButton" action="selectAction"/>
<button id="discardButton" action="discardAction"/>
</hbox>
<hbox spacing="true" width="100%">
<simplePagination id="pagination"
classNames="self-center pagination-right"
itemsPerPageVisible="true"
itemsPerPageDefaultValue="10"
dataLoader="certificatesDl" itemsPerPageUnlimitedItemVisible="true" autoLoad="true"/>
</hbox>
</layout>
</view>
public class CertificateListView extends StandardListView<Certificate> {
@ViewComponent
private CertificatesSummaryFragment certificatesSummaryFragment;
private Boolean isFirstLoad = true;
@Subscribe(id = "certificatesDl", target = Target.DATA_LOADER)
public void onCertificatesDlPostLoad(final CollectionLoader.PostLoadEvent<Certificate> event) {
if (Boolean.TRUE.equals(isFirstLoad)) {
isFirstLoad = false;
} else {
certificatesSummaryFragment.loadCertificatesSummary();
}
}
}
@FragmentDescriptor("certificates-summary-fragment.xml")
public class CertificatesSummaryFragment extends Fragment<FlexLayout> {
@ViewComponent
private Span draftCount;
@ViewComponent
private Span readyToSentCount;
@ViewComponent
private Span waitingResolutionCount;
@ViewComponent
private Span completedCount;
@ViewComponent
private Span rejectedCount;
@ViewComponent
private Span errorCount;
@ViewComponent
private Span revokedCount;
@ViewComponent
private Span totalCount;
private final DataManager dataManager;
public CertificatesSummaryFragment(DataManager dataManager) {
this.dataManager = dataManager;
}
@Override
@Subscribe
public void onAttach(AttachEvent event) {
loadCertificatesSummary();
}
public void loadCertificatesSummary() {
List<KeyValueEntity> kvEntities = dataManager.loadValues(
"select c.status, count(c.status) from Certificate c" +
" group by c.status")
.store("certificates")
.properties("status", "count")
.list();
Map<CertificateStatus, Long> counts = kvEntities.stream()
.collect(Collectors.toMap(
e -> CertificateStatus.fromId(e.getValue("status")),
e -> e.getValue("count")
));
draftCount.setText(String.valueOf(counts.getOrDefault(CertificateStatus.DRAFT, 0L)));
readyToSentCount.setText(String.valueOf(counts.getOrDefault(CertificateStatus.READY_TO_SENT, 0L)));
waitingResolutionCount.setText(String.valueOf(counts.getOrDefault(CertificateStatus.WAITING_RESOLUTION, 0L)));
completedCount.setText(String.valueOf(counts.getOrDefault(CertificateStatus.COMPLETED, 0L)));
rejectedCount.setText(String.valueOf(counts.getOrDefault(CertificateStatus.REJECTED, 0L)));
errorCount.setText(String.valueOf(counts.getOrDefault(CertificateStatus.ERROR, 0L)));
revokedCount.setText(String.valueOf(counts.getOrDefault(CertificateStatus.REVOKED, 0L)));
totalCount.setText(String.valueOf(counts.values().stream().mapToLong(Long::longValue).sum()));
}
}