Grid Filtering
(requires jqxgrid.filter.js)
To enable the filtering feature, you need to set the 'filterable' property to true and add a reference to the jqxgrid.filter.js file. When the value of the filterable property is true, the Grid displays a filtering panel in the columns popup menus. jqxGrid has several methods that allow you to handle the data filtering – addfilter, removefilter, applyfilters and clearfilters. The first method adds a filter to a grid column. The ‘removefilter’ method removes a filter from a grid column. The ‘applyfilters’ method applies all filters to the grid and refreshes its contents. The last method clears the filtering.
Let’s see how to add a filter:
1. The first step is to create a filter group. The filter group is a group of one or more filtering criterias.
2. The next step is to create the filters. Each filter must have a filter value – this is the value we compare each cell value with. The filter condition specifies how the filter will compare each cell value with the filter value. The filter condition value depends on the filter’s type(jqxGrid supports string, numeric and date filters). If you want to get the list of the supported grid filtering conditions, you can use the ‘getoperatorsbyfiltertype’ method of filter group.The createfilter method is used to create the filter.
3. The third step is to add the filters to the filter group. In the code example below, we added two filters in the filter group with operator ‘or’. This means that each cell value will be evaluated by filter1 and filter2 and the evaluation result will be true, if the filter1′s returned value is true or filter2′s returned value is true.
4. In the final step, we add the filter group to the first column and apply the filters by calling the ‘applyfilters’ method.
If you want to remove the filter, call the ‘removefilter’ method and then the ‘applyfilters’ method.
If you want to clear all filters, use the ‘clearfilters’ method.
For Server-Side filtering, see the
Server-side processing with PHP and MySQL help topic.
Custom Filter Conditions
To customzie the filter conditions, you need to do the following:
1. Update the Localization Strings. The filter conditions displayed in the filter dropdowns are loaded from the jqxGrid's localization object. *Bind to the bindingcomplete event before the Grid's initialization.
$("#jqxgrid").bind('bindingcomplete', function () {
var localizationobj = {};
filterstringcomparisonoperators = ['empty', 'not empty', 'contains', 'contains(match case)',
'does not contain', 'does not contain(match case)', 'starts with', 'starts with(match case)',
'ends with', 'ends with(match case)', 'equal', 'equal(match case)', 'null', 'not null'];
filternumericcomparisonoperators = ['equal', 'not equal', 'less than', 'less than or equal', 'greater than', 'greater than or equal', 'null', 'not null'];
filterdatecomparisonoperators = ['equal', 'not equal', 'less than', 'less than or equal', 'greater than', 'greater than or equal', 'null', 'not null'];
filterbooleancomparisonoperators = ['equal', 'not equal'];
localizationobj.filterstringcomparisonoperators = filterstringcomparisonoperators;
localizationobj.filternumericcomparisonoperators = filternumericcomparisonoperators;
localizationobj.filterdatecomparisonoperators = filterdatecomparisonoperators;
localizationobj.filterbooleancomparisonoperators = filterbooleancomparisonoperators;
// apply localization.
$("#jqxgrid").jqxGrid('localizestrings', localizationobj);
});
2. The second step is to update the conditions within the Filter object. In order to achieve that, you need to add a function called "updatefilterconditions". That function is invoked by
jqxGrid with 2 parameters - the type of the filter and the default filter conditions. As a result, the function should return an array of strings depending on the filter's type. That array will
be used by the filter object..
$("#jqxgrid").jqxGrid(
{
source: source,
filterable: true,
sortable: true,
updatefilterconditions: function (type, defaultconditions) {
var stringcomparisonoperators = ['EMPTY', 'NOT_EMPTY', 'CONTAINS', 'CONTAINS_CASE_SENSITIVE',
'DOES_NOT_CONTAIN', 'DOES_NOT_CONTAIN_CASE_SENSITIVE', 'STARTS_WITH', 'STARTS_WITH_CASE_SENSITIVE',
'ENDS_WITH', 'ENDS_WITH_CASE_SENSITIVE', 'EQUAL', 'EQUAL_CASE_SENSITIVE', 'NULL', 'NOT_NULL'];
var numericcomparisonoperators = ['EQUAL', 'NOT_EQUAL', 'LESS_THAN', 'LESS_THAN_OR_EQUAL', 'GREATER_THAN', 'GREATER_THAN_OR_EQUAL', 'NULL', 'NOT_NULL'];
var datecomparisonoperators = ['EQUAL', 'NOT_EQUAL', 'LESS_THAN', 'LESS_THAN_OR_EQUAL', 'GREATER_THAN', 'GREATER_THAN_OR_EQUAL', 'NULL', 'NOT_NULL'];
var booleancomparisonoperators = ['EQUAL', 'NOT_EQUAL'];
switch (type) {
case 'stringfilter':
return stringcomparisonoperators;
case 'numericfilter':
return numericcomparisonoperators;
case 'datefilter':
return datecomparisonoperators;
case 'booleanfilter':
return booleancomparisonoperators;
}
},
autoshowfiltericon: true,
columns: [
{ text: 'First Name', datafield: 'firstname', width: 100 },
{ text: 'Last Name', datafield: 'lastname', width: 100 },
{ text: 'Product', datafield: 'productname', width: 180 },
{ text: 'Order Date', datafield: 'date', width: 100, cellsformat: 'd' },
{ text: 'Quantity', datafield: 'quantity', width: 80, cellsalign: 'right' },
{ text: 'Unit Price', datafield: 'price', cellsalign: 'right', cellsformat: 'c2' }
]
});
Add filters to multiple columns through the API.
var data = generatedata(500);
var source =
{
localdata: data,
datatype: "array"
};
var addfilter = function () {
// create a filter group for the FirstName column.
var fnameFilterGroup = new $.jqx.filter();
// operator between the filters in the filter group. 1 is for OR. 0 is for AND.
var filter_or_operator = 1;
// create a string filter with 'contains' condition.
var filtervalue = 'Beate';
var filtercondition = 'contains';
var fnameFilter1 = fnameFilterGroup.createfilter('stringfilter', filtervalue, filtercondition);
// create second filter.
filtervalue = 'Andrew';
filtercondition = 'starts_with';
var fnameFilter2 = fnameFilterGroup.createfilter('stringfilter', filtervalue, filtercondition);
// add the filters to the filter group.
fnameFilterGroup.addfilter(filter_or_operator, fnameFilter1);
fnameFilterGroup.addfilter(filter_or_operator, fnameFilter2);
// add the filter group to the 'firstname' column in the Grid.
$("#jqxgrid").jqxGrid('addfilter', 'firstname', fnameFilterGroup);
// create a filter group for the Quantity column.
var quantityFilterGroup = new $.jqx.filter();
// create a filter.
var filter_or_operator = 1;
var filtervalue = 3;
var filtercondition = 'less_than';
var quantityFilter1 = quantityFilterGroup.createfilter('numericfilter', filtervalue, filtercondition);
quantityFilterGroup.addfilter(filter_or_operator, quantityFilter1);
// add the filter group to the 'quantity' column in the Grid.
$("#jqxgrid").jqxGrid('addfilter', 'quantity', quantityFilterGroup);
// apply the filters.
$("#jqxgrid").jqxGrid('applyfilters');
}
var adapter = new $.jqx.dataAdapter(source);
$("#jqxgrid").jqxGrid(
{
width: 670,
source: adapter,
filterable: true,
sortable: true,
ready: function () {
addfilter();
},
autoshowfiltericon: true,
columns: [
{ text: 'First Name', datafield: 'firstname', width: 90 },
{ text: 'Last Name', datafield: 'lastname', width: 90 },
{ text: 'Product', datafield: 'productname', width: 170 },
{ text: 'Order Date', datafield: 'date', width: 160, cellsformat: 'dd-MMMM-yyyy' },
{ text: 'Quantity', datafield: 'quantity', width: 80, cellsalign: 'right' },
{ text: 'Unit Price', datafield: 'price', cellsalign: 'right', cellsformat: 'c2' }
]
});
*generatedata function is implemented in the generatedata.js. The file is included in the download package and can be found in the installation_folder/demos/jqxgrid folder.
By default the filter groups are combined with 'AND' operator. That means that the Grid will display records if they meet the criteria of the filter group applied to column 1 and the filter group applied to column 2, etc.
You can create any combination of filter groups using 'AND' and 'OR'.
The example below illustrates how:
var data = generatedata(500);
var source =
{
localdata: data,
datatype: "array"
};
var addfilter = function () {
// create a filter group for the FirstName column.
var fnameFilterGroup = new $.jqx.filter();
fnameFilterGroup.operator = 'or';
// operator between the filters in the filter group. 1 is for OR. 0 is for AND.
var filter_or_operator = 1;
// create a string filter with 'contains' condition.
var filtervalue = 'Beate';
var filtercondition = 'contains';
var fnameFilter1 = fnameFilterGroup.createfilter('stringfilter', filtervalue, filtercondition);
// create second filter.
filtervalue = 'Andrew';
filtercondition = 'starts_with';
var fnameFilter2 = fnameFilterGroup.createfilter('stringfilter', filtervalue, filtercondition);
// add the filters to the filter group.
fnameFilterGroup.addfilter(filter_or_operator, fnameFilter1);
fnameFilterGroup.addfilter(filter_or_operator, fnameFilter2);
// add the filter group to the 'firstname' column in the Grid.
$("#jqxgrid").jqxGrid('addfilter', 'firstname', fnameFilterGroup);
// create a filter group for the Quantity column.
var quantityFilterGroup = new $.jqx.filter();
quantityFilterGroup.operator = 'or';
// create a filter.
var filter_or_operator = 1;
var filtervalue = 3;
var filtercondition = 'less_than';
var quantityFilter1 = quantityFilterGroup.createfilter('numericfilter', filtervalue, filtercondition);
quantityFilterGroup.addfilter(filter_or_operator, quantityFilter1);
// add the filter group to the 'quantity' column in the Grid.
$("#jqxgrid").jqxGrid('addfilter', 'quantity', quantityFilterGroup);
// apply the filters.
$("#jqxgrid").jqxGrid('applyfilters');
}
var adapter = new $.jqx.dataAdapter(source);
$("#jqxgrid").jqxGrid(
{
source: adapter,
filterable: true,
sortable: true,
ready: function () {
addfilter();
},
autoshowfiltericon: true,
columns: [
{ text: 'First Name', datafield: 'firstname', width: 90 },
{ text: 'Last Name', datafield: 'lastname', width: 90 },
{ text: 'Product', datafield: 'productname', width: 170 },
{ text: 'Order Date', datafield: 'date', width: 160, cellsformat: 'dd-MMMM-yyyy' },
{ text: 'Quantity', datafield: 'quantity', width: 80, cellsalign: 'right' },
{ text: 'Unit Price', datafield: 'price', cellsalign: 'right', cellsformat: 'c2' }
]
});
Using te above code, jqxGrid will display any record which meets at least one of the filtering criterias i.e a record is displayed, if it meets the condition of the 'FirstName' column's filter group or the one of the 'Quantity' column's filter group.