DataTable HTTP variables
This help article illustrates how to send data to the server. The DataTable builds a Data object to be sent to the server which contains data about the DataTable's current page number, sort columns, sort order, etc.
You can also add additional data variables. The data object passed by the widget is automatically converted to a query string by jQuery and it is appended to the url.
Example with data object
// prepare the data
var source =
{
datatype: "jsonp",
datafields: [
{ name: 'countryName' },
{ name: 'name' },
{ name: 'population', type: 'float' },
{ name: 'continentCode' }
],
url: "http://ws.geonames.org/searchJSON",
data: {
featureClass: "P",
style: "full",
maxRows: 50
}
};
The data is converted into a query string automatically though the
jQuery.param() method. The above code is appropriate only for static data.
Default variables sent by jQWidgets DataTable:
- sortdatafield - the sort column's datafield.
- sortorder - the sort order - 'asc', 'desc' or ''
- pagenum - the current page's number when the paging feature is enabled.
- pagesize - the page's size which represents the number of rows displayed in the view.
- filterslength - the number of filters applied to the DataTable
- filtervalue - the filter's value. The filtervalue name for the first filter is "filtervalue0", for the second filter is "filtervalue1" and so on.
- filtercondition - the filter's condition. The condition can be any of these: "CONTAINS", "DOES_NOT_CONTAIN", "EQUAL", "EQUAL_CASE_SENSITIVE",
NOT_EQUAL","GREATER_THAN", "GREATER_THAN_OR_EQUAL", "LESS_THAN", "LESS_THAN_OR_EQUAL", "STARTS_WITH",
"STARTS_WITH_CASE_SENSITIVE", "ENDS_WITH", "ENDS_WITH_CASE_SENSITIVE", "NULL", "NOT_NULL", "EMPTY", "NOT_EMPTY"
- filterdatafield - the filter column's datafield
- filteroperator - the filter's operator - 0 for "AND" and 1 for "OR"
Before the data is sent to the server, you can use the
formatData function of the jqxDataAdapter. The result returned by the
formatData function
will be sent to the server. The
formatData function is appropriate for scenarios with dynamic parameters.
Example with formatData
var source =
{
datatype: "jsonp",
datafields: [
{ name: 'countryName', type: 'string' },
{ name: 'name', type: 'string' },
{ name: 'population', type: 'float' },
{ name: 'continentCode', type: 'string' }
],
url: "http://ws.geonames.org/searchJSON"
};
var dataAdapter = new $.jqx.dataAdapter(source,
{
formatData: function (data) {
$.extend(data, {
featureClass: "P",
style: "full",
maxRows: 50
});
return data;
}
}
);
The parameter passed to the
formatData function represents the data object which includes all settings defined by you in the source object's
data member and all default settings of the DataTable('sortdatafield', 'sortorder', etc.).
Example with Server Paging, Sorting & Filtering
// prepare the data
var source =
{
dataType: "json",
dataFields: [
{ name: 'ShipCountry', type: 'string' },
{ name: 'ShipCity', type: 'string' },
{ name: 'ShipAddress', type: 'string' },
{ name: 'ShipName', type: 'string' },
{ name: 'Freight', type: 'number' },
{ name: 'ShippedDate', type: 'date' }
],
root: 'value',
url: "http://services.odata.org/V3/Northwind/Northwind.svc/Orders?$format=json"
};
var filterChanged = false;
var dataAdapter = new $.jqx.dataAdapter(source,
{
formatData: function (data) {
if (data.sortdatafield && data.sortorder) {
// update the $orderby param of the OData service.
// data.sortdatafield - the column's datafield value(ShipCountry, ShipCity, etc.).
// data.sortorder - the sort order(asc or desc).
data.$orderby = data.sortdatafield + " " + data.sortorder;
}
if (data.filterslength) {
filterChanged = true;
var filterParam = "";
for (var i = 0; i < data.filterslength; i++) {
// filter's value.
var filterValue = data["filtervalue" + i];
// filter's condition. For the filterMode="simple" it is "CONTAINS".
var filterCondition = data["filtercondition" + i];
// filter's data field - the filter column's datafield value.
var filterDataField = data["filterdatafield" + i];
// "and" or "or" depending on the filter expressions. When the filterMode="simple", the value is "or".
var filterOperator = data[filterDataField + "operator"];
var startIndex = 0;
if (filterValue.indexOf('-') == -1) {
if (filterCondition == "CONTAINS") {
filterParam += "substringof('" + filterValue + "', " + filterDataField + ") eq true";
filterParam += " " + filterOperator + " ";
}
}
else {
if (filterDataField == "ShippedDate") {
var dateGroups = new Array();
var startIndex = 0;
var item = filterValue.substring(startIndex).indexOf('-');
while (item > -1) {
dateGroups.push(filterValue.substring(startIndex, item + startIndex));
startIndex += item + 1;
item = filterValue.substring(startIndex).indexOf('-');
if (item == -1) {
dateGroups.push(filterValue.substring(startIndex));
}
}
if (dateGroups.length == 3) {
filterParam += "year(ShippedDate) eq " + parseInt(dateGroups[0]) + " and month(ShippedDate) eq " + parseInt(dateGroups[1]) + " and day(ShippedDate) eq " + parseInt(dateGroups[2]);
}
filterParam += " " + filterOperator + " ";
}
}
}
// remove last filter operator.
filterParam = filterParam.substring(0, filterParam.length - filterOperator.length - 2);
data.$filter = filterParam;
source.totalRecords = 0;
}
else {
if (filterChanged) {
source.totalRecords = 0;
filterChanged = false;
}
}
if (source.totalRecords) {
// update the $skip and $top params of the OData service.
// data.pagenum - page number starting from 0.
// data.pagesize - page size
data.$skip = data.pagenum * data.pagesize;
data.$top = data.pagesize;
}
return data;
},
downloadComplete: function (data, status, xhr) {
if (!source.totalRecords) {
source.totalRecords = data.value.length;
}
}
}
);
$("#dataTable").jqxDataTable(
{
width: 670,
pageable: true,
pagerButtonsCount: 10,
serverProcessing: true,
source: dataAdapter,
altRows: true,
filterable: true,
filterMode: 'simple',
sortable: true,
columnsResize: true,
columns: [
{ text: 'Ship Name', dataField: 'ShipName', width: 200 },
{ text: 'Ship Country', dataField: 'ShipCountry', width: 200 },
{ text: 'Ship City', dataField: 'ShipCity', width: 200 },
{ text: 'Ship Address', dataField: 'ShipAddress', width: 200 },
{ text: 'Ship Date', dataField: 'ShippedDate', width: 200, cellsFormat: 'yyyy-MM-dd' }
]
});
Notes:
- To enable the server processing of jqxDataTable, you need to set
serverProcessing to true.
- In server processing mode, jqxDataTable will make a data request on each Page change, Sort Order change or Filter change.
- The source object's
totalRecords member determines the records count. That parameter is used by the widget's Pager to display the correct amount of Pager buttons and to allow the end-user to navigate to a given page.
- The sample uses data from http://www.odata.org. OData is a standardized protocol for creating and consuming data APIs. OData builds on core protocols like HTTP and commonly accepted methodologies like REST. The result is a uniform way to expose full-featured data APIs.
- The data variables
$skip, $top, $orderby, $filter, year, eq, year, month, day are specific to the concrete sample and OData. They are not part of the jqxDataTable API.