Vue UI Components Documentation

Vue Server-Side Sorting with jQWidgets

This help topic demonstrates how to create a Vue Server-Side Sorting using the benefits of the jqxGrid. It will request data from the server for a new filter or when clearing the sorting. 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 sortdatafield and sortorder because we make a Server-Side Sorting 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. After that, when we sorting by one of the columns there will appear additional arguments in the query. 

filterscount=0&groupscount=0&sortdatafield=ShipName&sortorder=asc&pagenum=0&pagesize=10&recordstartindex=0&recordendindex=16

This is a simple example and it depends on the sorted column, in this case, the sorted column is ShipName (sortdatafield=ShipName) with ascending order (sortorder=asc). 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. The server should handle both cases - the default initialization of the jqxGrid and when it is sorting.


The Node.js Server source

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

In the above code, we create a query depending on the sort 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 with the sortdatafield we determinate which column is sorted and with the sortorder we check for the order direction.
let sortfield = query["sortdatafield"];
let sortorder = query["sortorder"];

Depending on these conditions we build our query. 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 Sorting

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:


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 Sorting.

  • 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 Sorting. 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