Grouping on Server
Enabling Server-Side Grouping with RestCollectionView
Server-Side Grouping is a useful feature that allows to group the data on Server and calculates aggregates for specified properties on Server. By extending RestCollectionView class, we can create a custom class called GroupRestCollectionView.
Supported operations
Server-Side Grouping: Groups data on the server
Server-Side Filtering and Sorting: Applies filters and sorting on the server side to optimize data retrieval.
Aggregate Functions: Supports server-side aggregation when grouping is enabled.
type=info
This feature is supported for FlexGrid, FlexSheet, MultiRow and Input controls like AutoComplete, ComboBox, ListBox
Extending RestCollectionView
For Server-Side Grouping feature, we'll need to initialize our variables and override getItems and getGroupItems method to fetch data from Server.
import { RestCollectionView } from '@mescius/wijmo.rest';
import {copy,httpRequest,asNumber,PropertyGroupDescription,CollectionViewGroup} from "@mescius/wijmo";
export class RestGroupCollectionView extends RestCollectionView{
_url: string;
constructor(url, options?) {
super();
this.groupOnServer = true;
this._url = url;
copy(this, options);
}
} The data received from Sever contains the dates in string format and to convert them to JavaScript Dates objects, we require the JSON Revier method.
// parse data
protected _jsonReviver(key: string, value: any): any {
const _rxDate = /^\d{4}\-\d{2}\-\d{2}T\d{2}\:\d{2}\:\d{2}|\/Date\([\d\-]*?\)/;
if (typeof value === 'string' && _rxDate.test(value)) {
value = value.indexOf('/Date(') == 0 // verbosejson
? new Date(parseInt(value.substr(6)))
: new Date(value);
}
return value;
}Before making the request to the server to retrieve the data, we must prepare the request parameters. To do this, we would write the _getReadParams method with the following parameters:
Filtering: Converts the filter criteria into OData format and applies it to the request.
Sorting: Constructs an order-by clause based on the sorting descriptions.
Grouping: Constructs a group-by clause and sorts the grouping properties
Sorting: Additional sorting logic is applied on top of the grouping.
Aggregation: Includes aggregate functions if required when grouping is enabled.
_getReadParams(groups: boolean): any {
let settings: any = {};
// apply filter
if (this.filterOnServer && this._filterProvider) {
let filter = this._asODataFilter(this._filterProvider);
if (filter.length > 0) {
settings.filterBy = filter;
}
}
// update groupBy
let gDescs = this.groupDescriptions;
if (this._groupOnServer && gDescs.length > 0) {
// fetch all groups
let _groupBy = [];
let _sortBy = [];
for (let i = 0; i < gDescs.length; i++) {
let _prop = (gDescs[i] as PropertyGroupDescription).propertyName;
_groupBy.push(_prop);
_sortBy.push(`${_prop} ASC`);
}
if (groups) // check if groupby clause required or not
settings.groupBy = _groupBy;
settings.orderBy = _sortBy.join(',');
}
//update orderBy for group descriptions
if (this.sortDescriptions.length > 0) {
let _sortBy = [];
//check existing sort
if (settings.orderBy) {
_sortBy = settings.orderBy.split(',');
}
for (let i = 0; i < this.sortDescriptions.length; i++) {
let sort = `${this.sortDescriptions[i].property} ${this.sortDescriptions[i].ascending ? 'ASC' : 'DESC'}`;
var _cSrtIdx = _sortBy.findIndex(x => x.indexOf(this.sortDescriptions[i].property) > -1);
if (_cSrtIdx > -1)
_sortBy[_cSrtIdx] = sort; // update existing sort
else
_sortBy.push(sort);// add new sort
}
settings.orderBy = _sortBy.join(',');
}
// set aggregates if required (only with server side grouping )
if (groups && this.aggregates && this.aggregates.length > 0 && this.groupDescriptions.length > 0 && settings.groupBy) {
settings.aggregates = this.aggregates;
}
return settings;
}type=info
The filters are converted to OData format using the _asODataFilter function. If your data source does not support ODataFormat, you can create your own method to transform the filter query to the data source's acceptable format.
_asODataFilter method code can be get from RestCollectionView\OData Demo sample’s rest-collection-view-odata.js file
As the request parameters are ready, now we are ready to send the request to the Server to fetch the data and groups with aggregates. For this, getItems method would be overridden to get data items and getGroupItems to fetch groups with aggregates.
protected getItems(): Promise<any[]> {
// cancel any pending requests
if (this._pendingReq) {
this._pendingReq.abort();
}
return new Promise<any>(resolve => {
let _settings = this._getReadParams(false); // get the items virtually
this._pendingReq = httpRequest(this._url, {
requestHeaders: this.requestHeaders,
data: _settings,
success: async xhr => {
// parse response
let resp = JSON.parse(xhr.responseText, this._jsonReviver);
let _count = asNumber(resp.totalItemCount);
if (_count != this._totalItemCount)
this._totalItemCount = _count;
resolve(resp.items);
},
error: xhr => this._raiseError(xhr.responseText, false),
complete: xhr => { this._pendingReq = null; }// no pending requests
});
});;
}
_pendingRequest: XMLHttpRequest;
//fetch group items
protected getGroupItems(item?: CollectionViewGroup): Promise<any[]> {
// cancel any pending requests
if (this._pendingRequest) {
this._pendingRequest.abort();
}
return new Promise<any>(resolve => {
let _settings = this._getReadParams(true);
if (this.groupDescriptions.length > 0) {
httpRequest(this._url, {
requestHeaders: this.requestHeaders,
data: _settings,
success: async xhr => {
// parse response
let re = xhr.responseText;
let resp = JSON.parse(xhr.responseText, this._jsonReviver);
if (resp.totalGroupCount) {
this._totalGroupItemCount = asNumber(resp.totalGroupCount);
}
let _count = asNumber(resp.totalItemCount);
if (_count != this._totalItemCount)
this._totalItemCount = _count;
resolve(resp.groupItems);
},
error: xhr => this._raiseError(xhr.responseText, false),
complete: xhr => this._pendingRequest = null // no pending requests
});
}
});
}Now, we're able to call the RESTCollectionView in our JavaScript file and use that as our data source for your FlexGrid control:
// Extended RESTCollectionView class
import { GroupRestCollectionView } from './group-rest-collection-view';
import { FlexGrid } from '@mescius/wijmo.grid';
function init(){
let cv = new GroupRestCollectionView(url,{
aggregates: `Sum(actualCost) as actualCost,Sum(quantity) as quantity`, // perform aggregation for groups
groupDescriptions: [
// group description to add groups
new PropertyGroupDescription('productName'),
new PropertyGroupDescription('transactionType')
]
}); // create CollectionView Instance
let grid = new FlexGrid("#virtualGrid", {
autoGenerateColumns: false,
columns: [
{ binding: 'productId', header: 'Product ID', width: '' },
{ binding: 'color', header: 'Color', width: '' },
{ binding: 'modifiedDate', header: 'Modified Date', dataType: 'Date', format: 'd' },
{ binding: 'quantity', header: 'Quantity', dataType: 'Number', format: 'n2' },
{ binding: 'actualCost', header: 'Actual Cost', dataType: 'Number', format: 'n2',aggregate:'Sum'}
],
itemsSource:cv // assign Custom Collection View Instance
});
}You can check a sample implementation with server and client code Wijmo-Rest-CollectionView-Sample