Pivot view

This adds a pivot endpoint to API v2 controllers, effectively enabling Excel's Pivot table-like support.

Pivot is based on group by and aggregate functions, while still respecting the same filters as the list endpoint.

The endpoint signature is as follows pivot(@ParameterObject final Pagination page, @RequestBody(required = false) final F filter, @ParameterObject final PivotRequest pivot) where:

@Data
public class PivotRequest {
  /** The order of group-bys is important. */
  @NotNull
  @NotEmpty
  private List<String> groupBy;

  /** Aggregations to generate. */
  @NotNull
  @NotEmpty
  private List<PivotAggregate> aggregations;
}

/** The type of aggregation */
public enum Aggregation {
  SUM,
  COUNT,
  COUNT_DISTINCT,
  AVERAGE,
}

@Data
public class PivotAggregate {
  private String field;
  private Aggregation as;
}

The implementation generates a query (similar to list) but instead of selecting all entity fields, it selects for the aggregated properties in aggregation:

// I want to see: by site and by inventory form type, how many different unit codes are used and how many inventories are there:

pivotRequest = {
  groupBy = [
    "site",
    "formTypeCode"
  ],
  aggregations = [
    { field: "quantityOnHandUnitCode", as: "COUNT_DISTINCT" },
    { field: "id", as: "COUNT" }
  ]
}

// This generates a select (the ... represent retrieving the property from Qxxx):
select(
  // Group bys
  ...("site"),
  ...("formTypeCode"),
  // Aggregations
  ...("quantityOnHandUnitcode").countDistinct(),
  ...("id").count(),
)

It then applies the filters using where. Then applies the group-bys:

// adds groupBy statements to the query
.groupBy(
  ...("site"),
  ...("formTypeCode")
)

And sorts and fetches the requested Page where page.sort determines the sorting order.

Processing the query result

We need to return to the UI results that are organized for easy rendering. The API response is a Map<String, ?> where each groupBy represents a new sub-map:

{
  123: { // site id (group by) as key
    "SD": [ // formTypeCode (group by) as key
      10, // count distinct(formTypeCode) as value
      300 // count(id) as value
    ],
    "IV": [ // formTypeCode (group by) as key
      1, // count dist..
      15 // count(id) as value
    ]
...
}

The UI should be able to reconstruct, based on the PivotRequest, a tabular display of these results. For example:

• The number of elements in the $.123.SD array is 2, resulting in a rowSpan += 2
• The number of keys in $.123 is also two, resulting in rowSpan += 2 = 4 for site = 123.