React UI Components Documentation

React Server-Side Filtering

This tutorial shows how to create a Server-Side Filtering for jqxGrid with React. It will request data from the server for a new filter or when clearing the filtering. The Server-Side script will provide the related data as a JSON object.

Overview

We will create our simple server with the powerful Node.js platform, we will handle our queries and get the data from the familiar Northwind database. For this purpose, we need to include a few plugins to achieve that we want - like express and mysql for our server script. About the Client-Side, we will use the jqxGrid on the React library. We assume that this tutorial is known and we will continue from this position with the next steps.

Getting Started

The next step is to install the desired plugins. For this purpose, we open the terminal and navigate to the root of the current project. 

/root
    /my-app
    /node_modules
    server.js

I. Let's install the express package. In the terminal use the command below: 

npm install express

II. Install the mysql package. Enter the following command: 

npm install mysql


Review of the current state

The package.json file should have dependencies for the newly added plugins. How to create it you can look at this.

Start our XAMPP server

We need to connect with our database. In our tutorial, we will use the Northwind database. For this purpose we will use the XAMPP with the same success we could use WAMP or another alternative. Here we will skip the steps of the installation of these platforms because we assume that this is known already.

XAMPP running example

We need to start the first two options - Apache and MySQL as in the image above. This is enough to proceed with the next steps. To create one fully working example.

Server-Side - Handling requests with Node.js

The prepared example with jqxGrid will make requests to the server with a bunch of extra arguments. The jqxGrid automatically passes the Sort, Page and Filter parameters (Read more about that here). Important for us now are filterscount, filtervalue, filtercondition, filterdatafield and filteroperator because we make a Server-Side Filtering scenario. We will create a server.js file that will do the mentioned above. The file will be in one level up of the React my-app folder.

In this file, we connect to the Northwind database. The default port React project running is 3000 and for this purpose we will set the port of the Node.js server to the 4545. 

// server.js
const mysql = require('mysql');
const express = require("express");
const app = express();
const PORT = 4545;
 
let connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "northwind"
});
 
app.get("/getdata", (req, res) => {
    res.header('Access-Control-Allow-Origin', req.headers.origin || "*");
    res.header('Access-Control-Allow-Methods', 'GET,POST,PUT,HEAD,DELETE,OPTIONS');
    res.header('Access-Control-Allow-Headers', 'content-Type,x-requested-with');
 
    let simpleQuery = "SELECT OrderDate, ShippedDate, ShipName, ShipAddress, ShipCity, ShipCountry FROM Orders";
 
    if (Object.entries(req.query).length === 0 && req.query.constructor === Object) {
        connection.query(simpleQuery, (error, records) => {
            if (error) {
                res.json({ "error": true });
            } else {
                res.json(records);
            }
        });
    } else {
        let query = req.query;
        if (!!query.filterscount && query.filterscount > 0) {
            let filterscount = query["filterscount"];
            let where = " WHERE (";
            let tmpdatafield = "";
            let tmpfilteroperator = "";
            let valuesPrep = "";
            let values = [];
            for (let i = 0; i < filterscount; i++) {
                const filtervalue = query["filtervalue" + i];
                const filtercondition = query["filtercondition" + i];
                const filterdatafield = query["filterdatafield" + i];
                const filteroperator = query["filteroperator" + i];
                if (tmpdatafield == "") {
                    tmpdatafield = filterdatafield;
                } else if (tmpdatafield != filterdatafield) {
                    where += ")AND(";
                } else if (tmpdatafield == filterdatafield) {
                    if (tmpfilteroperator == 0) {
                        where += " AND ";
                    } else {
                        where += " OR ";
                    }
                }
 
                // build the "WHERE" clause depending on the filter's condition, value and datafield.
                let condition = "";
                let value = "";
                switch (filtercondition) {
                    case "CONTAINS":
                        condition = " LIKE ";
                        value = "%" + filtervalue + "%";
                        break;
                    case "DOES_NOT_CONTAIN":
                        condition = " NOT LIKE ";
                        value = "%" + filtervalue + "%";
                        break;
                    case "EQUAL":
                        condition = " = ";
                        value = filtervalue;
                        break;
                    case "NOT_EQUAL":
                        condition = " <> ";
                        value = filtervalue;
                        break;
                    case "GREATER_THAN":
                        condition = " > ";
                        value = filtervalue;
                        break;
                    case "LESS_THAN":
                        condition = " < ";
                        value = filtervalue;
                        break;
                    case "GREATER_THAN_OR_EQUAL":
                        condition = " >= ";
                        value = filtervalue;
                        break;
                    case "LESS_THAN_OR_EQUAL":
                        condition = " <= ";
                        value = filtervalue;
                        break;
                    case "STARTS_WITH":
                        condition = " LIKE ";
                        value = "" + filtervalue + "%";
                        break;
                    case "ENDS_WITH":
                        condition = " LIKE ";
                        value = "%" + filtervalue + "";
                        break;
                    case "NULL":
                        condition = " IS NULL ";
                        value = "%" + filtervalue + "%";
                        break;
                    case "NOT_NULL":
                        condition = " IS NOT NULL ";
                        value = "%" + filtervalue + "%";
                        break;
                }
 
                where += " " + filterdatafield + condition + "? ";
                valuesPrep += valuesPrep + "s";
                values.push(value);
                if (i == filterscount - 1) {
                    where += ")";
                }
 
                tmpfilteroperator = filteroperator;
                tmpdatafield = filterdatafield;
            }
 
            values.forEach(element => {
                where = where.replace("?", "'" + element + "'");
            });
 
            simpleQuery += where;
        }
 
        connection.query(simpleQuery, (error, result) => {
            if (error) {
                res.json({ "error": true, "message": error });
            } else {
                res.json(result);
            }
        });
    }
});
 
app.listen(PORT, _ => {
    console.log("The server is running on PORT: ", PORT);
});

Client-Side - Visualizing the data in jqxGrid

We have a lot of examples in our React demos section, especially for the jqxGrid. Which can be used to create the desired structure and to use the most suitable approach for us. Also, we suppose that we already are familiar with the Create jQWidgets React App TSX tutorial. Based on these points we will start directly with the editing. Navigate to the root/my-app/src folder. Let's make the following changes:

App.tsx: 

import * as React from 'react';
 
import JqxGrid, { IGridProps, jqx } from 'jqwidgets-scripts/jqwidgets-react-tsx/jqxgrid';
 
class App extends React.PureComponent<{}, IGridProps> {
    private myGrid = React.createRef<JqxGrid>();
    private url: string = 'http://localhost:4545/getdata';
    private source: any = {
        url: this.url,
        datafields: [
            { name: 'ShipName', type: 'string' },
            { name: 'ShipAddress', type: 'string' },
            { name: 'ShipCity', type: 'string' },
            { name: 'ShipCountry', type: 'string' }
        ],
        datatype: 'json',
        cache: false,
        filter: (data: any) => {
            // update the grid and send a request to the server.
            this.myGrid.current!.updatebounddata('filter');
        }
    };
 
    private columns: IGridProps['columns'] =
        [
            { text: 'Ship Name', datafield: 'ShipName', width: 200 },
            { text: 'Address', datafield: 'ShipAddress', width: 200 },
            { text: 'City', datafield: 'ShipCity', width: 150 },
            { text: 'Country', filtertype: 'list', datafield: 'ShipCountry' }
        ];
 
    constructor(props: {}) {
        super(props);
 
        this.state = {
            columns: this.columns,
            source: new jqx.dataAdapter(this.source)
        };
    }
 
    public render() {
        return (
            <JqxGrid ref={this.myGrid}
                width={800}
                height={450}
                source={this.state.source}
                filterable={true}
                showfilterrow={true}
                columns={this.state.columns}
            />
        );
    }
}
 
export default App;

Summary

We use a lot of different technologies to achieve Server-Side Filtering.

  • XAMPP - to connect to the MySQL with the Northwind database
  • Node.js - to create a simplified server application
    • express plugin - handling different routes
    • mysql plugin - for SQL queries to the database
  • React - our Client-Side technology
  • jqxGrid - visualize the data from the database

Required Final Steps

We should run everything and we should have one fully working jqxGrid React example with Server-Side Filtering. The XAMPP should be launched as we described before.

The next step is to run our Node.js server. Open the terminal with the root directory (/root/server.js) of our application and type: 

node server.js

This will execute our server.js file and our server will run.

Now we should open another terminal with the root (/root/my-app/) of our React project. Type the familiar command: 

npm start

This should run our React project at the following port: 

http://localhost:3000/

Final Result

Final Result