Skip to main content

Aggregation

Apply provided functions to values to calculate group values in the grid.

Backend source code of example grids available here

Supported Aggregation Functions​

The following built-in functions are supported: avg, sum, min, max, count.

warning

The first and last aggregation functions are not supported in JPA. Using them will throw a runtime exception.

Enabling Aggregation​

To make a column available for aggregation, set the enableValue parameter to true on ColDef, otherwise an aggregation attempt on this column will result in a runtime exception.

To limit which aggregation functions (AggregationFunction) can be used on the column, provide an array of allowed functions (otherwise all aggregation functions are allowed).

ColDef priceColumn = ColDef.builder()
    .field("price")
    .enableValue(true)
    .allowedAggFuncs(AggregationFunction.avg, AggregationFunction.count)
    // or just use string names
    // .allowedAggFuncs("avg", "count")
    .build();
  • Source code for this grid available here
Loading grid...

Custom Aggregation Functions​

In addition to the built-in functions, the adapter allows you to register custom aggregation functions or overwrite existing ones. This is particularly useful for leveraging database-specific functions (e.g., statistical functions) that are not part of the standard JPA set.

Custom functions are registered within the QueryBuilder using the .registerCustomAggFunction() method. The following example demonstrates registering functions specifically for the Postgres database.

QueryBuilder<Entity> queryBuilder = QueryBuilder.builder(Entity.class, entityManager)
    .colDefs(colDefs)
    // Registering custom aggregation functions
    .registerCustomAggFunction("bool_and", (cb, expr) -> cb.function("BOOL_AND", Boolean.class, expr))
    .registerCustomAggFunction("stddev_pop", (cb, expr) -> cb.function("STDDEV_POP", BigDecimal.class, expr))
    .registerCustomAggFunction("stddev_samp", (cb, expr) -> cb.function("STDDEV_SAMP", BigDecimal.class, expr))
    .build();
  • Source code for this grid available here
Loading grid...

Aggregation - Filtering​

Filtering can be configured to impact aggregate values in the grid.

Ignore Filters when Aggregating​

When using Filters and Aggregations together, the aggregated values reflect only the rows which have passed the filter. This can be changed to instead ignore applied filters by using the suppressAggFilteredOnly grid option.

QueryBuilder<Entity> queryBuilder = QueryBuilder.builder(Entity.class, entityManager)
    .colDefs(
        // ...col defs
    )
    .suppressAggFilteredOnly(true)
    .build();
  • Source code for this grid available here
Loading grid...

Filtering for Aggregated Values​

The grid only applies filters to leaf level rows, this can be toggled to instead also apply filtering to group rows by enabling the groupAggFiltering grid option, allowing filters to also apply against the aggregated values.

QueryBuilder<Entity> queryBuilder = QueryBuilder.builder(Entity.class, entityManager)
    .colDefs(
        // ...col defs
    )
    .groupAggFiltering(true)
    .build();
  • When a group row passes a filter, it also includes all of its descendent rows in the filtered results.
  • The suppressAggFilteredOnly grid option will be implicitly enabled.
  • Source code for this grid available here
Loading grid...