We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to perform advanced filtering in grid using custom queries

Platform: JavaScript - EJ 2 |
Control: Grid |
Published Date: March 23, 2020 |
Last Revised Date: March 27, 2020
Tags: grid, ej2grid

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 = [];
    }
}
 

 

Example Queries

Some of the examples were listed below.

1)CategoryName = Beverages And ProductName = Chai

2)ProductName = [Chai, Chang, Ipoh Coffee, Lakkalikööri] And UnitsInStock = [39]

Output

Custom Filtering action

Figure 1: Custom Filtering Action

Typescript demo: https://stackblitz.com/edit/qehbhm-a8jhsg?file=index.ts

React Demo: https://stackblitz.com/edit/react-5rvnnb?file=index.js

Angular Demo: https://stackblitz.com/edit/angular-kkdufm?file=app.component.ts

 

 

 

 

ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile