How to perform advanced filtering in grid using custom queries

5 mins read

Solution

Apart from the default filtering and searching of the Grid, we can perform advanced filtering using custom queries. This can be done by generating the custom Predicates of the Query. Later, these queries can be used to filter the data in the DataManager. We have implemented this functionality using the external events from the Input elements.

Define the Required Elements

Place the Div Element for Grid, Input text element for placing the query and to control the action use the Button.

    <input id="advanced-search" /> 
    <input value='search' id='search' type='button' />
    <div id="Grid"></div>

Define the Grid

Define the Grid and Textbox component with the Button.

import { enableRipple } from '@syncfusion/ej2-base';
enableRipple(true);
import { DataManager, Query, Predicate } from '@syncfusion/ej2-data';
import { InputObject, TextBox } from '@syncfusion/ej2-inputs';
import { Grid, Filter, Page, Selection } from '@syncfusion/ej2-grids';
import { categoryData } from './data-source';
 
import { Button } from '@syncfusion/ej2-buttons';
Grid.Inject(Page, Selection);
 
let grid: Grid = new Grid(
    {
        dataSource: categoryData,
        allowPaging: true,
        columns: [
            { field: 'CategoryName', headerText: 'Category Name', width: 160 },
            { field: 'ProductName', headerText: 'Product Name', width: 170 },
            { field: 'QuantityPerUnit', headerText: 'Quantity Per Unit', width: 170, textAlign: 'Right' },
            { field: 'UnitsInStock', headerText: 'Units In Stock', width: 170, textAlign: 'Right' },
            {
                field: 'Discontinued', headerText: 'Discontinued', width: 150,
                textAlign: 'Center', displayAsCheckBox: true, type: 'boolean'
            }
        ],
        pageSettings: { pageCount: 5 }
    });
grid.appendTo('#Grid');
 
let searchText: TextBox = new TextBox({
    placeholder: 'Query Here',
    cssClass: 'e-outline',
    floatLabelType: 'Auto'
});
 
 
searchText.appendTo('#advanced-search');
let button: Button = new Button();
button.appendTo('#search');

Define Action events for the Search

You have to get the text from the Textbox and process the string to the required Predicate Values. For processing and data generation, you can bind the click event to the Button component. Along with this declare the required variables.

 
let predicate: Predicate[] = [];
let mulPredicate: Predicate[] = [];
 
document.getElementById('search').addEventListener('click', () => {
    searchFn()
})
 
 
let searchFn = () => {
    var searchVal = searchText.value;
    if (searchVal === "" || searchVal === undefined) {
        grid.dataSource = categoryData;
    } else {
        var pattern = new RegExp("and", 'gi');
        var queries = searchText.value.split(pattern);
        var i = 0;
        while (i < queries.length) {
            var query = queries[i].split("=");
            query[0] = query[0].trim();
            query[1] = query[1].trim();
            var querylen = query[1].length;
            if (query[1].indexOf('[') === 0 && query[1].indexOf(']') === querylen - 1) {
                var splitVals = query[1].replace('[', "").replace(']', "").split(",");
                var j = 0;
                while (j < splitVals.length) {
                    splitVals[j] = splitVals[j].trim();
                    mulPredicate = (mulPredicate.length === 0) ? new Predicate(query[0], 'contains', splitVals[j]) : (mulPredicate as any).or(query[0], 'contains', splitVals[j]);
                    j++;
                }
                predicate = (predicate.length === 0) ? mulPredicate : (predicate as any).and(mulPredicate);
                mulPredicate = []
            } else {
                predicate = (predicate.length === 0) ? new Predicate(query[0], 'contains', query[1]) : (predicate as any).and(query[0], 'contains', query[1]);
            }
            i++;
        }
        new DataManager({
            json: categoryData
        }).executeQuery(new Query().where(predicate)).then((e: any) => {
            grid.dataSource = e.result;
        })
        predicate = [];
    }
}