固定とピン留め
Enabling virtualization with Server-Side Grouping using RestCollectionView
Server-Side Grouping is a useful feature that allows to group the data on Server and calculates aggregates for specified properties on Server. Further, the virtualization provides a powerful mechanism for handling large datasets by virtualizing data requests. This feature is especially useful when dealing with large collections where loading all data at once would be inefficient.
By extending RestCollectionView class, we can create a custom class called GroupRestCollectionView that would allow to load the grouped data in chunks with virtualization.
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.
Virtualization: Efficiently handles large datasets by only loading the necessary data portions.
Aggregate Functions: Supports server-side aggregation when grouping is enabled.
This feature is supported for FlexGrid, FlexSheet, MultiRow.
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} from "@mescius/wijmo";
export class GroupRestCollectionView extends RestCollectionView {
_url: string = '';
constructor(url, options?) {
super(options);
this._url = url;
this.groupOnServer = true;
this.virtualization = true;
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.
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(groupInfo: boolean = false, virtualParams: boolean = true): any {
let gDescs = this.groupDescriptions;
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
if (this.groupOnServer && gDescs.length > 0) {
// send single request to fetch all groups when virtualization only is true
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 (groupInfo)
settings.groupBy = _groupBy;
settings.orderBy = _sortBy.join(',');
}
//update orderBy
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(',');
}
//
if (virtualParams && this.virtualization) {
settings.skip = this._start;
settings.top = this._fetchSize();
}
// set aggregates property if required (only with groupBy)
if (groupInfo && this.aggregates && this.aggregates.length > 0 && this.groupDescriptions.length > 0) {
settings.aggregates = this.aggregates;
}
return settings;
}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(); // 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(): Promise<any[]> {
// cancel any pending requests
if (this._pendingRequest) {
this._pendingRequest.abort();
}
return new Promise<any>(resolve => {
let _settings = this._getReadParams(true, false);
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
});
}Note:
This feature cannot be used in combination with group lazy-loading feature.
Pagination is not supported.
You can check a sample implementation with server and client code Wijmo-Rest-CollectionView-Sample