Vue UI Components Documentation

Vue Server-Side Filtering with jQWidgets

This help topic demonstrates how to create a Vue Server-Side Filtering using the benefits of the jqxGrid. 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 use 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 Vue.js framework. 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.

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.

Server-Side - Handling requests with Node.js

The prepared example with jqxGrid will make requests to the server with a bunch of extra arguments. Important for us now are filterscount, filtervalue, filtercondition, filterdatafield and filteroperator because we make a Server-Side Filtering scenario.

Create the file

We have to take and process these requests. For this, we will create a server.js file that will handle our requests from the jqxGrid. This file in our application will be created at the same level as the root of the Vue project start - ./my-app/server.js. With the help of the Node.js, we will start the server and handle the queries to the database.

Including libraries

Open the server.js file and after that include the express and mysql: 

// server.js

const mysql = require('mysql');
const express = require("express");

Prepare Express

Now we should create our small internal application from Express - it will be something like a middleware. Let's create it on that way: 

const app = express();

The Express is useful for handling the routing. We will use it to process our URL requests from the jqxGrid. More details about this you can find on the official site.


Configuration of the connection to the server

Firstly we should connect with the database. Let's add this to the server.js file: 

let connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "northwind"
});

Note: Configuration to the database could vary.


Routing

What are Routes?
It determinates the way that one application response to the client's requests to a particular endpoint.

As we mentioned before we will have extra HTTP variables. In the default case when the jqxGrid is initialized the query string will look like that: 

filterscount=0&groupscount=0&pagenum=0&pagesize=10&recordstartindex=0&recordendindex=16

That will be the initial request state of our jqxGrid. As we mentioned we should handle a simple case of Client-Side request. In the next step, we should include the code below in the server.js file to handle this. Another case will be the filtering - we need to extract the parameters and the values related to this and to make a query to the server.


The Node.js Server source

The content of the server.js file should look like as this below: 

// server.js
 
const mysql = require('mysql');
const express = require("express");
const app = express();
const PORT = 3000;
 
let connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "northwind"
});
 
app.get("/getdata", (req, res) => {
    // With these rows below we enable all request to our server
    // Otherwise, we will see the CORS error message
    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);
});

In the above code, we create a query depending on the filter expression. The jqxGrid sends the following data to the server:

  • 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.
  • groupscount - the number of groups in the Grid
  • group - the group's name. The group's name for the first group is 'group0', for the second group is 'group1' and so on.
  • filterscount - the number of filters applied to the Grid
  • 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"

After we extract the needed variables then we prepare our query and send it to the database. In the server.js file, we check for the filterscount and is its value is bigger than 0. The express plugin provides objects request with a query member from which we could extract the wanted parameters. Then we iterate through all of the applied filters. For example, with this code below we get the filter value for index i:
const filtervalue = query["filtervalue" + i];

The same approach we use for the filtercondition ('Contains', 'Starts With', etc.) of the current filter:
const filtercondition = query["filtercondition" + i];
The current filter's column is stored in the 'filterdatafield' property. const filterdatafield = query["filterdatafield" + i];
The filter operator specifies the relation of the current filter with the filters from the same column. Its value can be 0 for "AND" and 1 for "OR".
const filteroperator = query["filteroperator" + i];

Depending on these conditions we build our query's WHERE clause. With the mysql plugin, we use the connection object to send the query to the database - connection.query. If we have a successful result we return to the Client-Side via this code:
res.json(result);

Client-Side - Creating jqxGrid with Filtering

We have a lot of examples in our Vue 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 Vue App tutorial. Based on these points we will start directly with the editing of the App.vue file. Open it and replace its content with the following: 

<template>
    <JqxGrid
        ref="myGrid"
        :width="800"
        :height="450"
        :source="dataAdapter"
        :filterable="true"
        :showfilterrow="true"
        :columns="columns"
    />
</template>
 
<script>
    import JqxGrid from "jqwidgets-scripts/jqwidgets-vue/vue_jqxgrid.vue";
    
    export default {
        components: {
            JqxGrid
        },
        data: function () {
            return {
                // eslint-disable-next-line
                dataAdapter: new jqx.dataAdapter(this.source),
                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" }
                ]
            };
        },
        beforeCreate: function () {
            const url = "http://localhost:3000/getdata";
            this.source = {
                url: url,
                datafields: [
                    { name: "ShipName", type: "string" },
                    { name: "ShipAddress", type: "string" },
                    { name: "ShipCity", type: "string" },
                    { name: "ShipCountry", type: "string" }
                ],
                datatype: "json",
                root: "Rows",
                cache: false,
                filter: () => {
                    // update the grid and send a request to the server.
                    this.$refs.myGrid.updatebounddata("filter");
                }
            };
        }
    };
</script>
    
<style src="./assets/styles/jqwidgets/jqx.base.css"></style>
<style src="./assets/styles/jqwidgets/jqx.material-green.css"></style>

With every change we make a new request to our Node.js server that we already handled above. It responds with a specific data segment.

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
  • Vue.js - our Client-Side wrapper
  • jqxGrid - visualize the data from the database

Required Final Steps

We should run everything and we should have one fully working jqxGrid Vue 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 of our application and type: 

node server.js

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

Server.js Running

Now we should open another terminal with the root (the same) of our Vue project. Type the familiar command: 

npm run serve
Vue.js Running

This should run our Vue project as follow: 

App running at:
    - Local:   http://localhost:8080/
    - Network: http://192.168.1.108:8080/

If you have followed the tutorial then you can start one of the above URL addresses to see the final result.

Final Result

Final Result