Ajax Pagination Node.js MySQL RestAPIs Example – Ajax + Nodejs (Express + Sequelize) + MySQL

Ajax Nodejs MySQL Pagination RestAPIs Example Tutorial

In the real life, we always meet a large dataset and we want to present it to the user in smaller chunks, pagination and sorting is often helpful solution. So in the tutorial, I introduce how to build an Ajax Pagination in Node.js Application with very clearly steps and running sourcecode.

Related posts:


Overview Ajax Pagination in Node.js MySQL RestAPIs Example + Filtering & Sorting

Architecture Design Ajax Node.js Application

Ajax Nodejs MySQL Pagination RestAPIs - Overall Architecture
Ajax Nodejs MySQL Pagination RestAPIs – Overall Architecture

– Nodejs gets data from MySQL then provides RestAPIs with pagination, filtering and sorting function for Ajax javascript Client.
– Client use Ajax to fetch data from Nodejs RestAPIs then shows them in HTML Bootstrap table.

Goal for Ajax Pagination in Node.js

– Make a request at API: /api/customers/pagefiltersort with pagination, filtering and sorting params as below:

  • page: 0 – first page
  • size: 5 – size of a page
  • salary: 4000 – filtering by salary field
  • agesorting: true – sorting by age
  • desc: true – descending or ascending sorting

– Result:

Nodejs MySQL Pagination RestAPIs Examples
Nodejs MySQL Pagination RestAPIs Examples

– Ajax Pagination in Nodejs – Client View:

Nodejs MySQL Ajax Pagination Example
Nodejs MySQL Ajax Pagination Example

Video Guide

Server Side Pagination in Node.js in MySQL

Related post:

Tutorial Nodejs Express RestAPIs MySQL with Sequelize Queries Examples

Overall Node.js Sequelize MySQL Pagination

To handling Pagination RestAPI requests and do Paging Filtering and Sorting queries with MySQL database, we create a backend web Node.js application with 4 main points:

Server Side Node.js Express Pagination Example
Server Side Node.js Express Pagination Example
  • To handle pagination RestAPI requests with Node.js, We use Express framework.
  • To do pagination filtering and sorting queries with MySQL database, We use Sequelize ORM.
  • We define all RestAPI URLs in router.js.
  • We implement code how to process each paging filtering and sorting RestAPI request in controller.js file.

Nodejs Sequelize Pagination Queries

To do the pagination with database, Sequelize ORM provides 2 model methods for supporting the purpose with limit and offset parameters:

  1. .findAll() – Search for multiple elements in the database
  2. .findAndCountAll() - Search for multiple elements in the database, returns both data and total count

How about limit & offset for nodejs pagination?

  • limit is the maximum number of records to fetch
  • offset is the quantity of records to skip

For example, if we have total 12 items:

  • { offset: 5 }: skip first 5 items, fetch 7 remaining items.
  • { limit: 5 }: fetch first 5 items.
  • { offset: 5, limit: 5 }: skip first 5 items, fetch 6th and 10th items.

Sequelize findAll() method

Here is an query example with the Sequelize .findAll({offset, limit}) method:

Debug with Sequelize .FindAll Pagination Method
Debug with Sequelize .FindAll Pagination Method

The .findAll() method return an array of 7 items from id=8 to id=14 (the first 7 items are skips because the offset = page*limit = 7).

Testing Pagination with Sequelize findAll Method
Testing Pagination with Sequelize findAll Method

Using a Native SQL query to double check the above results:

Execute Native SQL Query to Double Check the Returned Results of Squelize findAll() Query
Execute Native SQL Query to Double Check the Returned Results of Squelize findAll() Query

– We do NOT know how many next items we can fetch more when doing the pagination with .findAll() method.

-> Let’s go with next session: how to work with Sequelize .findAndCountAll() method for finding the new solution!

Sequelize findAndCountAll() method

Here is a query example with the Sequelize .findAndCountAll({offset, limit}) method:

Debug Nodejs Pagination with Sequelize findAndCountAll() method
Debug Nodejs Pagination with Sequelize findAndCountAll() method

This is a convenience method that combines .findAll() and count, this is useful when dealing with queries related to pagination where you want to retrieve data with a limit and offset but also need to know the total number of records that match the query:

The success handler will always receive an object type with two properties:

  • count is the total number records matching the where clause and other filters due to associations
  • rows – an array of objects, the records matching the where clause and other filters due to associations, within the limit and offset range

So the Sequelize method .findAndCountAll({...}) is the best solution for building Node.js RestAPIs with pagination filtering and sorting functions.

How to build Node.js Express Pagination MySql

For building the Node.js Pagination project, we follow 5 steps for development:

  1. Setup Nodejs Project
  2. Create an Express WEB Application Server
  3. Define All RestAPI URLs in router.js
  4. Configure MySQL Database with Sequelize ORM
  5. Implement All RESTAPIs in controllers.js file

Create Ajax Pagination Nodejs Project

Before creating a Nodejs project, we need to confirm that the Nodejs and npm had been installed in your computer development by cmd: node -v and npm -v

Nodejs Environment Setup for Development
Nodejs Environment Setup for Development

If these commandlines are not recognized by command prompt, it means you need to install them by visit the https://nodejs.org/en/ site and download installed package and do the nodejs setup for development later.

Now starting development! Create a folder and named it as Nodejs-Pagination-Filtering-Sorting, go inside the folder, open a cmd and initiate a Nodejs project by cmd npm init. After all, a package.json file is created as below content:

{
  "name": "nodejs-pagination-filtering-sorting",
  "version": "1.0.0",
  "description": "Nodejs pagination filtering sorting",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "repository": {
    "type": "git",
    "url": "git+https://github.com/github.com/loizenjava.git"
  },
  "keywords": [
    "nodejs",
    "filtering",
    "sorting",
    "pagination"
  ],
  "author": "https://loizenjava.com",
  "license": "ISC",
  "bugs": {
    "url": "https://github.com/github.com/loizenjava/issues"
  },
  "homepage": "https://github.com/github.com/loizenjava#readme",
  "dependencies": {
    "body-parse": "^0.1.0",
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "mysql2": "^2.1.0",
    "sequelize": "^6.3.3"
  }
}

For coding Editor, we use friendly Visual Studio Code to write code and debug Nodejs project. For the tutorial, We will create a Ajax Pagination in Node.js project with below structure:

Nodejs Ajax Pagination Project Structure
Nodejs Ajax Pagination Project Structure

- Group 1: Nodejs RestAPIs coding

  1. file db.config.js is used to define MySQL database configuration with Sequelize ORM
  2. file customer.model.js is used to define a Sequelize model mapped with corresponding MySQL database table schema.
  3. file router.js is used to define all Express RestAPI URLs
  4. file controller.js is used to implement detail logic code to process each incoming request
  5. file server.js is used to implement a Nodejs Web server

- Group 2: JQuery Ajax and HTML Pagination Client

  1. view/index.html: using Bootstrap and Html to structure the client pagination view
  2. resources/js/table.js: using JQuery Ajax to fetch JSON data from Node.js RestAPIs and render on Client

To development a Nodejs RestAPIs with MySQL database, we need a set of packages to handle the full stack of the web backend proccessing, they includes Express framework, Cors, Body Parse, MySQL packages and Sequelize ORM.

We can install all the packages by one cmd:

$npm install --save express cors body-parser mysql2 sequelize

For more details about "Express framework, Cors, Body Parse, MySQL packages and Sequelize ORM", you can refer here.

Create Express Application Server

To implement an Express RestAPIs Application, firstly we need create a server with express app:

- server.js

const express = require('express');
const app = express();
...
const server = app.listen(8080, function () {
 
  let host = server.address().address
  let port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port); 
})

For parsing body of requests, we need use body-parser dependency, add more code on server.js file:

...
var bodyParser = require('body-parser');
...
app.use(bodyParser.json());
...
const server = app.listen(8080, function () {
...

We define all RESTAPI URLs in a file router.js and then need attach it with the Express Application:

...
let router = require('./app/routers/router.js');
...
app.use('/', router);
...
const server = app.listen(8080, function () {
...

To enable CORS function, we need attach it with Express app:

const cors = require('cors')
const corsOptions = {
  origin: 'http://localhost:4200',
  optionsSuccessStatus: 200
}
app.use(cors(corsOptions));

To add resources folder as static folder files of Express Application, use a line of code:
- app.use(express.static('resources'));

Here is all coding in the server.js file:

const express = require('express');
const app = express();

var bodyParser = require('body-parser');
 
const db = require('./app/config/db.config.js');

global.__basedir = __dirname;
  
// force: true will drop the table if it already exists
db.sequelize.sync({force: true}).then(() => {
  console.log('Drop and Resync with { force: true }');
}); 

let router = require('./app/routers/router.js');

const cors = require('cors')
const corsOptions = {
  origin: 'http://localhost:4200',
  optionsSuccessStatus: 200
}
app.use(cors(corsOptions));

app.use(bodyParser.json());
app.use(express.static('resources'));
app.use('/', router);

// Create a Server
const server = app.listen(8080, function () {
 
  let host = server.address().address
  let port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port); 
})

Define All RestAPI URLs in router.js

For making a router with Express framework, firstly we need get the router object from Express app. Then using it to define the GET/POST/DELETE/PUT requests:

let express = require('express');
let router = express.Router();
 
const customers = require('../controllers/controller.js');

let path = __basedir + '/view/';

router.get('/', (req,res) => {
    console.log("__basedir" + __basedir);
    res.sendFile(path + "index.html");
});

router.post('/api/customers/create', customers.create);
router.get('/api/customers/pagefiltersort', customers.pagingfilteringsorting);
router.get('/api/customers/salaries', customers.getSalaries);

module.exports = router;

Use the express.Router class to create modular, mountable route handlers. A Router instance is a complete middleware and routing system.

Configure MySQL Database with Sequelize ORM

Firstly, we create a file env.js with an const Object to include all configured parameters for MySQL database setup.

const env = {
  database: 'loizenjavadb',
  username: 'root',
  password: '12345',
  host: 'localhost',
  dialect: 'mysql',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

module.exports = env;

Now for the main setting between MySQL database and Sequelize ORM, we define a file db.config.js as below code:

const env = require('./env.js');
 
const Sequelize = require('sequelize');
const sequelize = new Sequelize(env.database, env.username, env.password, {
  host: env.host,
  dialect: env.dialect,
  operatorsAliases: false,
 
  pool: {
    max: env.max,
    min: env.pool.min,
    acquire: env.pool.acquire,
    idle: env.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;
 
db.Customer = require('../models/customer.model.js')(sequelize, Sequelize);
 
module.exports = db;

Implement All RESTAPIs in controllers.js file

Nodejs RestAPIs Controller.js File
Nodejs RestAPIs Controller.js File

For handling Nodejs RestAPIs' processing that been defined in router.js file, we implement all 3 working functions in controller.js file:

  1. create = (req, res) function is used to handle a GET request at the endpoint /api/customers/create to post a new Customer entity to MySQL database
  2. exports.getSalaries = (req, res) function is used to handle a GET request at the endpoint /api/customers/getSalaries and returns a list of distinct salaries from database
  3. pagingfilteringsorting = (req, res) function is used to handle a GET request at the endpoint /api/customers/pagefiltersort to do an association quering operation: pagination, plus filtering by salary and sorting by age

Nodejs Sequelize Pagination RestAPIs

pagingfilteringsorting = (req, res) function is used to handle a GET request at the endpoint /api/customers/pagefiltersort to do an association quering operation: pagination, plus filtering by salary and sorting by age from database.

We use the method .findAndCountAll(...) to do the associated operation: pagination plus filtering and sorting query. So we add more 2 clauses inside .findAndCountAll(...) method:

  • Where clause - using to filter by salary field: where: {salary: salary}
  • Order clause - using to sort rows by 2 fileds age with descending direction or not:
    
    order: [
      ['age', 'DESC']
    ],
    

Straightforward steps to implement the Pagination + Filtering and Sorting function:

  • Retrieve 5 parameters from incoming request query: limit, page for pagination and salary for filtering and agesorting & desc for sorting
  • Calulate an offset for Sequelize pagining query later:
    const offset = page ? page * limit : 0;
  • Do the Sequelize pagination filtering and sorting with .findAndCountAll() method:
    Customer.findAndCountAll({
                                attributes: ['id', 'firstname', 'lastname', 'age', 'address', 'salary', 'copyrightby'],
                                where: {salary: salary}, 
                                order: [
                                  ['age', 'DESC']
                                ],
                                limit: limit, 
                                offset:offset 
                              })
  • Construct a Json message with informative fields and return back to client side:
    const response = {
      "copyrightby": "https://loizenjava.com",
      "totalPages": totalPages,
      "pageNumber": page,
      "pageSize": result.rows.length,
      "customers": result.rows
    };
    res.send(response);
  • Do NOT forget to handle an un-expected errors with try-catch statement.
    try{
        //...
    }catch(error) {
        res.status(500).send({
          message: "Error -> Can NOT complete a paging request!",
          error: error.message,
        });
    }   

- Coding here:

exports.pagingfilteringsorting = async (req, res) => {
  try{
    let page = parseInt(req.query.page);
    let limit = parseInt(req.query.size);
    let agesorting = (req.query.agesorting === 'true');
    let desc = (req.query.desc === 'true');
    let salary = req.query.salary ? parseInt(req.query.salary) : -1;
  
    const offset = page ? page * limit : 0;

    console.log("offset = " + offset);    

    let result = {};

    // NOT Filtering with salary
    if(salary < 0 ){
      // not sorting with age
      if(agesorting == false) {
        result = await Customer.findAndCountAll({
          attributes: ['id', 'firstname', 'lastname', 'age', 'address', 'salary', 'copyrightby'],
          limit: limit, 
          offset:offset 
        });
      } else {
        if(desc == false) { // sorting with age and ascending
          result = await Customer.findAndCountAll({
            attributes: ['id', 'firstname', 'lastname', 'age', 'address', 'salary', 'copyrightby'],
            limit: limit, 
            offset:offset,
            order: [
              ['age', 'ASC']
            ]             
          });
        } else { // sorting with age and descending
          result = await Customer.findAndCountAll({
            attributes: ['id', 'firstname', 'lastname', 'age', 'address', 'salary', 'copyrightby'],
            limit: limit, 
            offset:offset,
            order: [
              ['age', 'DESC']
            ]             
          });
        }
      }
    } else { // Filtering with salary
      // not sorting with age
      if(agesorting == false) {
        result = await Customer.findAndCountAll({
          attributes: ['id', 'firstname', 'lastname', 'age', 'address', 'salary', 'copyrightby'],
          where: {salary: salary},
          limit: limit, 
          offset:offset
        });
      } else {
        if(desc == false) { // sorting with age and ascending
          result = await Customer.findAndCountAll({
            attributes: ['id', 'firstname', 'lastname', 'age', 'address', 'salary', 'copyrightby'],
            where: {salary: salary},
            limit: limit, 
            offset:offset,
            order: [
              ['age', 'ASC']
            ]                         
          });
        } else { // sorting with age and descending
          result = await Customer.findAndCountAll({
            attributes: ['id', 'firstname', 'lastname', 'age', 'address', 'salary', 'copyrightby'],
            where: {salary: salary},
            limit: limit, 
            offset:offset,
            order: [
              ['age', 'DESC']
            ]                         
          });
        }
      }      
    }

    const totalPages = Math.ceil(result.count / limit);
    const response = {
      "copyrightby": "https://loizenjava.com",
      "totalPages": totalPages,
      "pageNumber": page,
      "pageSize": result.rows.length,
      "customers": result.rows
    };
    res.send(response);
  }catch(error) {
    res.status(500).send({
      message: "Error -> Can NOT complete a paging request!",
      error: error.message,
    });
  }      
}

- Result:

Nodejs Pagination RestAPIs Testing
Nodejs Pagination RestAPIs Testing

Nodejs GET all Distinct Salary RestAPIs

getSalaries = (req, res) function is used to handle a GET request at the endpoint /api/customers/getSalaries and returns a list of distinct salaries from database:

exports.getSalaries = (req, res) => {
  Customer.findAll({
    attributes: [
      [Sequelize.fn('DISTINCT', Sequelize.col('salary')), 'salary'],
    ],
    order: [
      ['salary', 'ASC']
    ],                         
  }).then(result => {
    let salaries = result.map(x => x.salary);
    res.send(salaries);
  }).catch(error => {
    res.status(500).send({
      message: "Error -> Can NOT get all customer's salaries",
      error: error.message
    });
  });
}

- Result:

Nodejs RestAPI Get List Of Distinct Salaries
Nodejs RestAPI Get List Of Distinct Salaries

Pagination Ajax Javascript with Bootstrap Table – Frontend Development

Review HTML Pagination Page

Layout HTML Page - Ajax Pagination with Bootstrap Table
Layout HTML Page - Ajax Pagination with Bootstrap Table

We define the view page with 4 parts:

  • Bootstrap Table is used to display Customer list with table format
  • Pagination Bar is used to list all data pages
  • Filtering Selection is used to select a specific salary to do the pagination with filtering
  • Sorting Checkboxes is used to determine whether the sorting with age field or Not and sorting with descending or ascending(default value) direction

Related post with
Pagination and Ajax:

SpringBoot Tutorial – Ajax Pagination Filtering and Sorting

HTML Bootstrap Pagination Table Implementation

We use Bootstrap framework to style our pagination view, so we need to add the below bootstrap links to head tag in index.html file:

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>

Bootstrap Pagination Table

We define a Bootstrap table with 7 columns to show all the properties of a Customer object:

  • Id
  • Firstname
  • Lastname
  • Age
  • Salary
  • Address
  • Copyright
<table id="customerTable" class="table table-hover table-sm">
	<thead class="thead-dark">
		<tr>
			<th>Id</th>
			<th>Firstname</th>
			<th>Lastname</th>
			<th>Age</th>
			<th>Salary</th>
			<th>Address</th>
			<th>Copyright By</th>
		</tr>
	</thead>
	<tbody>
	</tbody>
</table>

Filtering Selection

We use the html select form to create a salary filtering list as below code:

<div class="form-group">
  <label for="slected_form"><h5>Filtering with Salary:</h5></label>
  <select class="form-control" id="selected_form">
  </select>
</div>

Sorting Checkboxes

We use html checkboxes to define 2 fields to determine for sorting or not:

  • agesorting = true (means be checked) -> do the sorting by age field
  • desc = true (mean be checked) -> do sorting with descending direction, (default is ascending direction)

Here is the html coding:

<form>
  <div class="custom-control custom-checkbox mb-3">
    <input type="checkbox" class="custom-control-input" id="age_sorting" name="age_sorting">
    <label class="custom-control-label" for="age_sorting">Age</label>
  </div>
  <input type="checkbox" id="desc_sorting" name="desc_sorting" disabled>
  <label for="defaultCheck">Desc</label>
  <br>
</form>
<button id="sortingbtn" class="btn btn-primary" disabled>Sort Now</button>

Full HTML View Page

We create a index.html file in src/main/resources as below code:

<!DOCTYPE html>
<html lang="en">
<head>
<title>Ajax Paging Table</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
<script type="text/javascript" src="/js/table.js"></script>
</head>
<body>
	<div class="container">
		<div class="row">
			<div class="col-sm-7"
					style="background-color: #e6f9ff; margin: 10px; padding: 10px; border-radius: 5px">
				<div class="alert alert-danger">
					<h3>Bootstrap + Ajax + Nodejs Pagination</h3>
					<hr>
					<p>
						<strong>@Copyright</strong> by <span style="color: blue">
						<a href="https://loizenjava.com">https://loizenjava.com
						</a></span>
						 <br> 
						<strong>youtube</strong>: <span style="color: crimson">
						<a href="https://www.youtube.com/channel/UChkCKglndLes1hkKBDmwPWA">loizenjava
						</a></span>
					</p>
				</div>
				<h5>Do you want sorting by salary?</h5>
			    <form>
			      <div class="custom-control custom-checkbox mb-3">
			        <input type="checkbox" class="custom-control-input" id="age_sorting" name="age_sorting">
			        <label class="custom-control-label" for="age_sorting">Age</label>
			      </div>
			      <input type="checkbox" id="desc_sorting" name="desc_sorting" disabled>
			      <label for="defaultCheck">Desc</label>
			      <br>
			    </form>
			    <button id="sortingbtn" class="btn btn-primary" disabled>Sort Now</button>
				<hr>
				<div class="form-group">
				  <label for="slected_form"><h5>Filtering with Salary:</h5></label>
				  <select class="form-control" id="selected_form">
				  </select>
				</div>
	
				<table id="customerTable" class="table table-hover table-sm">
					<thead class="thead-dark">
						<tr>
							<th>Id</th>
							<th>Firstname</th>
							<th>Lastname</th>
							<th>Age</th>
							<th>Salary</th>
							<th>Address</th>
							<th>Copyright By</th>
						</tr>
					</thead>
					<tbody>
					</tbody>
				</table>
				
				<ul class="pagination justify-content-center" style="margin:20px 0">
				</ul>
			</div>
		</div>
	</div>
</body>
</html>

Jquery Ajax Pagination JSON

We use Ajax of JQuery to fetch data from Nodejs RestAPIs with pagination, filtering and sorting.
And using JQuery Javascript to control the data in html Bootstrap view.

Ajax Fetching Data from Remote RestAPI

For getting data with pagination, filtering and sorting from Nodejs restAPIs, we implement a JQuery Fuction with Ajax code that includes a list of 5 needed parameters:
- Signature of the function: function fetchCustomers(page, size, salary, agesorting, desc)

  • page: number of page for requesting
  • size: a size of a data page for requesting
  • salary: a selected salary for filtering
  • agesorting: a flag to determine to do the sorting by age field or not
  • desc: a flag to determine the direction for sorting, desc = true for descending sorting direction and desc = false for ascending sorting direction (default value)
function fetchCustomers(page, size, salary, agesorting, desc){
	let pageNumber = (typeof page !== 'undefined') ?  page : 0;
	let sizeNumber = (typeof size !== 'undefined') ?  size : 5;
	let selectedsalary = (typeof salary !== 'undefined') ?  salary : -1;
	let ageSorted = (typeof agesorting !== 'undefined') ?  agesorting: false;
	let descDirection = (typeof desc !== 'undefined') ?  desc: false;

	/**
	 * Do a fetching to get data from Backend's RESTAPI
	 */
    $.ajax({
        type : "GET",
        url : "/api/customers/custom/pageable",
        data: { 
            page: pageNumber, 
            size: sizeNumber,
            salary: selectedsalary,
            agesorting: ageSorted,
            desc: descDirection
        },
        success: function(response){
          $('#customerTable tbody').empty();
          // add table rows
          $.each(response.customers, (i, customer) => {  
            let tr_id = 'tr_' + customer.id;
            let customerRow = '<tr>' +
      	  						  '<td>' + customer.id + '</td>' +
		                		  '<td>' + customer.firstname + '</td>' +
		                		  '<td>' + customer.lastname + '</td>' +
		                		  '<td>' + customer.age + '</td>' +
		                          '<td>' + '$' + customer.salary + '</td>' +
		                          '<td>' + customer.address + '</td>' +
		                          '<td>' + '<a href="https://loizenjava.com">' + customer.copyrightBy + '</a>' + '</td>' +
		                       '</tr>';
            $('#customerTable tbody').append(customerRow);
          });              
          
          if ($('ul.pagination li').length - 2 != response.totalPages){
          	  // build pagination list at the first time loading
        	  $('ul.pagination').empty();
              buildPagination(response.totalPages);
          }
        },
        error : function(e) {
          alert("ERROR: ", e);
          console.log("ERROR: ", e);
        }
    });    	
}

The fetchCustomers function calls an Ajax request to the URL api/customers/custom/pageable:

$.ajax({
    type : "GET",
    url : "/api/customers/custom/pageable",
    data: { 
        page: pageNumber, 
        size: sizeNumber,
        salary: selectedsalary,
        agesorting: ageSorted,
        desc: descDirection
    },

If successfully, it will render a list of response's Customers on defined-table by code:

success: function(response){
  $('#customerTable tbody').empty();
  // add table rows
  $.each(response.customers, (i, customer) => {  
    let tr_id = 'tr_' + customer.id;
    let customerRow = '<tr>' +
  						  '<td>' + customer.id + '</td>' +
                		  '<td>' + customer.firstname + '</td>' +
                		  '<td>' + customer.lastname + '</td>' +
                		  '<td>' + customer.age + '</td>' +
                          '<td>' + '$' + customer.salary + '</td>' +
                          '<td>' + customer.address + '</td>' +
                          '<td>' + '<a href="https://loizenjava.com">' + customer.copyrightBy + '</a>' + '</td>' +
                       '</tr>';
    $('#customerTable tbody').append(customerRow);
  });  

And building the pagination list via the response variable's property totalPages as below code:

  if ($('ul.pagination li').length - 2 != response.totalPages){
  	  // build pagination list at the first time loading
	  $('ul.pagination').empty();
      buildPagination(response.totalPages);
  }

- buildPagination(totalPages) function:

/**
 * 
 * Build the pagination Bar from totalPages
 */
function buildPagination(totalPages){
    // Build paging navigation
    let pageIndex = '<li class="page-item"><a class="page-link">Previous</a></li>';
    $("ul.pagination").append(pageIndex);
    
    // create pagination
    for(let i=1; i <= totalPages; i++){
  	  // adding .active class on the first pageIndex for the loading time
  	  if(i==1){
      	  pageIndex = "<li class='page-item active'><a class='page-link'>"
  				+ i + "</a></li>"            		  
  	  } else {
      	  pageIndex = "<li class='page-item'><a class='page-link'>"
	  				+ i + "</a></li>"
  	  }
  	  $("ul.pagination").append(pageIndex);
    }
    
    pageIndex = '<li class="page-item"><a class="page-link">Next</a></li>';
    $("ul.pagination").append(pageIndex);
}

Ajax with Pagination Bar

When do a click on the pagination bar, we have 3 cases to determine:

  • Next
  • Previous
  • a Specific Index Page

And we need to re-fecth the right pagination data from the remote RestAPI to build fill again the right data in html view page:

$(document).on("click", "ul.pagination li a", function() {
	let agesorting = false;
	let desc = false;
	let selectedSalary = getSeletedSalary();
	if($("#age_sorting"). prop("checked") == true){
		agesorting = true;
	}
	
	if($("#desc_sorting"). prop("checked") == true){
		desc = true;
	}
	
	let val = $(this).text();
	
	// click on the NEXT tag
  	if(val.toUpperCase()==="NEXT"){
  		let activeValue = parseInt($("ul.pagination li.active").text());
  		let totalPages = $("ul.pagination li").length - 2; // -2 beacause 1 for Previous and 1 for Next 
  		if(activeValue < totalPages){
  			let currentActive = $("li.active");
  			fetchCustomers(activeValue, 5, selectedSalary, agesorting, desc); // get next page value
  			// remove .active class for the old li tag
  			$("li.active").removeClass("active");
  			// add .active to next-pagination li
  			currentActive.next().addClass("active");
  		}
  	} else if(val.toUpperCase()==="PREVIOUS"){
  		let activeValue = parseInt($("ul.pagination li.active").text());
  		if(activeValue > 1){
  			// get the previous page
  			fetchCustomers(activeValue-2, 5, selectedSalary, agesorting, desc);
  			let currentActive = $("li.active");
  			currentActive.removeClass("active");
  			// add .active to previous-pagination li
  			currentActive.prev().addClass("active");
  		}
  	} else {
  		fetchCustomers(parseInt(val) - 1, 5,  selectedSalary, agesorting, desc);
  		// add focus to the li tag
  		$("li.active").removeClass("active");
  		$(this).parent().addClass("active");
  	}
});

Do Ajax Pagination with Filtering

We need to build the list salaries with distinct values, so we need to call an Ajax function getListSalaries() at the initial time:

/**
 * Get a list of distinct salaries
 */
function getListSalaries(){
	$.ajax({
        type : "GET",
        url : "/api/customers/salaries",
        success: function(response){
          $("#selected_form").empty();
          $('#selected_form').append("<option>All</option>");
          $.each(response.sort().reverse(), (i, salary) => {
        	// <option>All</option>
            let optionElement = "<option>" + salary + "</option>";
            $('#selected_form').append(optionElement);
          });              
        },
        error : function(e) {
          alert("ERROR: ", e);
          console.log("ERROR: ", e);
        }
	});
}    

If having any change from the salay filtering select-form, the fetchCustomers() function will be invokes again with the selected salary for filtering the right data to paging and sorting. Here is the detail function:

/**
 * Select a salary for pagination & filtering
 */
$("select").change(function() {
	let salary = -1;
	
	if(isNumeric(this.value)){
		salary = this.value;
	}
	
	let agesorting = false;
	let desc = false;
	
	if($("#age_sorting"). prop("checked") == true){
		agesorting = true;
	}
	
	if($("#desc_sorting"). prop("checked") == true){
		desc = true;
	}
	
	// re-fetch customer list again 
    fetchCustomers(0, 5, salary, agesorting, desc);
});

Do Ajax Pagination with Sorting

With the sorting checkboxes, we implement 2 functions for handling the pagination and sorting:

  • $('#age_sorting').on('change', function(){...})
  • $(document).on("click", "#sortingbtn", function() {...});

- $('#age_sorting').on('change', function(){...}) is used to control the disable attribute and the property checkbox checked of desc and sortingbtn button

/**
 * age_sorting checkbox is changed
 */
$('#age_sorting').on('change', function() {
    if(this.checked){
    	$("#desc_sorting").removeAttr("disabled");
    	$("#sortingbtn").removeAttr("disabled");
    }else {
    	$("#desc_sorting").attr("disabled", true);
    	$("#desc_sorting").prop("checked", false);
    	$("#sortingbtn").attr("disabled", true);
    }
}); 

- $(document).on("click", "#sortingbtn", function() {...}); is used to fetch againt the pagination function with sorting parameters agesorting and desc: fetchCustomers(selectedPageIndex, 5, selectedSalary, agesorting, desc);

- Coding:

$(document).on("click", "#sortingbtn", function() {
	let agesorting = false;
	let desc = false;
	let selectedSalary = getSeletedSalary();
	
	//get value of check boxes
	
	/* agesorting checkbox */
	if($("#age_sorting"). prop("checked") == true){
		agesorting = true;
	}
	
	/* desc checkbox */
	if($("#desc_sorting"). prop("checked") == true){
		desc = true;
	}
	
	// get the active index of pagination bar 
	let selectedPageIndex = parseInt($("ul.pagination li.active").text()) - 1;
	
	// just fetch again customers from Nodejs RestAPIs when agesorting checkbox is checked
	if(agesorting){
		fetchCustomers(selectedPageIndex, 5, selectedSalary, agesorting, desc); // get next page value	
	}
});

Full Ajax Script

We implement all JQuery Ajax code in a file table.js which are placed at /static/js/ folder. Here is the full sourcecode:

$(document).ready(function(){	
	function fetchCustomers(page, size, salary, agesorting, desc){
		let pageNumber = (typeof page !== 'undefined') ?  page : 0;
		let sizeNumber = (typeof size !== 'undefined') ?  size : 5;
		let selectedsalary = (typeof salary !== 'undefined') ?  salary : -1;
		let ageSorted = (typeof agesorting !== 'undefined') ?  agesorting: false;
		let descDirection = (typeof desc !== 'undefined') ?  desc: false;
	
		/**
		 * Do a fetching to get data from Backend's RESTAPI
		 */
		$.ajax({
		    type : "GET",
		    url : "/api/customers/custom/pageable",
		    data: { 
		        page: pageNumber, 
		        size: sizeNumber,
		        salary: selectedsalary,
		        agesorting: ageSorted,
		        desc: descDirection
		    },
			success: function(response){
			  $('#customerTable tbody').empty();
			  // add table rows
			  $.each(response.customers, (i, customer) => {  
			    let tr_id = 'tr_' + customer.id;
			    let customerRow = '<tr>' +
			  						  '<td>' + customer.id + '</td>' +
			                		  '<td>' + customer.firstname + '</td>' +
			                		  '<td>' + customer.lastname + '</td>' +
			                		  '<td>' + customer.age + '</td>' +
			                          '<td>' + '$' + customer.salary + '</td>' +
			                          '<td>' + customer.address + '</td>' +
			                          '<td>' + '<a href="https://loizenjava.com">' + customer.copyrightBy + '</a>' + '</td>' +
			                       '</tr>';
			    $('#customerTable tbody').append(customerRow);
			  });              
	          
			  if ($('ul.pagination li').length - 2 != response.totalPages){
			  	  // build pagination list at the first time loading
				  $('ul.pagination').empty();
			      buildPagination(response.totalPages);
			  }
	        },
	        error : function(e) {
	          alert("ERROR: ", e);
	          console.log("ERROR: ", e);
	        }
	    });    	
	}

    /**
     * Check a string value is a number or NOT
     */
    function isNumeric(value) {
        return /^-{0,1}\d+$/.test(value);
    }
    
	/**
	 * Select a salary for pagination & filtering
	 */
	$("select").change(function() {
		let salary = -1;
		
		if(isNumeric(this.value)){
			salary = this.value;
		}
		
		let agesorting = false;
		let desc = false;
		
		if($("#age_sorting"). prop("checked") == true){
			agesorting = true;
		}
		
		if($("#desc_sorting"). prop("checked") == true){
			desc = true;
		}
		
		// re-fetch customer list again 
	    fetchCustomers(0, 5, salary, agesorting, desc);
	});

	/**
	 * Get a list of distinct salaries
	 */
	function getListSalaries(){
		$.ajax({
	        type : "GET",
	        url : "/api/customers/salaries",
	        success: function(response){
	          $("#selected_form").empty();
	          $('#selected_form').append("<option>All</option>");
	          $.each(response.sort().reverse(), (i, salary) => {
	        	// <option>All</option>
	            let optionElement = "<option>" + salary + "</option>";
	            $('#selected_form').append(optionElement);
	          });              
	        },
	        error : function(e) {
	          alert("ERROR: ", e);
	          console.log("ERROR: ", e);
	        }
		});
	}    
    
	/**
	 * age_sorting checkbox is changed
	 */
	$('#age_sorting').on('change', function() {
	    if(this.checked){
	    	$("#desc_sorting").removeAttr("disabled");
	    	$("#sortingbtn").removeAttr("disabled");
	    }else {
	    	$("#desc_sorting").attr("disabled", true);
	    	$("#desc_sorting").prop("checked", false);
	    	$("#sortingbtn").attr("disabled", true);
	    }
	}); 
    
    /**
     * Click on sorting Button
     */
	$(document).on("click", "#sortingbtn", function() {
		let agesorting = false;
		let desc = false;
		let selectedSalary = getSeletedSalary();
		
		//get value of check boxes
		
		/* agesorting checkbox */
		if($("#age_sorting"). prop("checked") == true){
			agesorting = true;
		}
		
		/* desc checkbox */
		if($("#desc_sorting"). prop("checked") == true){
			desc = true;
		}
		
		// get the active index of pagination bar 
		let selectedPageIndex = parseInt($("ul.pagination li.active").text()) - 1;
		
		// just fetch again customers from Nodejs RestAPIs when agesorting checkbox is checked
		if(agesorting){
			fetchCustomers(selectedPageIndex, 5, selectedSalary, agesorting, desc); // get next page value	
		}
	});
    
	/**
	 * 
	 * Build the pagination Bar from totalPages
	 */
	function buildPagination(totalPages){
	    // Build paging navigation
	    let pageIndex = '<li class="page-item"><a class="page-link">Previous</a></li>';
	    $("ul.pagination").append(pageIndex);
	    
	    // create pagination
	    for(let i=1; i <= totalPages; i++){
	  	  // adding .active class on the first pageIndex for the loading time
	  	  if(i==1){
	      	  pageIndex = "<li class='page-item active'><a class='page-link'>"
	  				+ i + "</a></li>"            		  
	  	  } else {
	      	  pageIndex = "<li class='page-item'><a class='page-link'>"
		  				+ i + "</a></li>"
	  	  }
	  	  $("ul.pagination").append(pageIndex);
	    }
	    
	    pageIndex = '<li class="page-item"><a class="page-link">Next</a></li>';
	    $("ul.pagination").append(pageIndex);
	}
    
    /**
     * Get the selectedSalary for filtering
     */
    function getSeletedSalary(){
    	if(!isNumeric($("select").val())){
    		return -1;
    	}else return parseInt($("select").val());
    }
    
    /**
     * 
     * Fetching the Customers from Nodejs RestAPI at the initial time
     */
    (function(){
    	// get first-page at initial time
    	fetchCustomers(0);
    	
    	// get the distinct values of customer's salaries
    	getListSalaries();
    })();
        
    /**
     * Fetch again the customer's data from RestAPI when 
     * 		having any click on pagination bar for pagination filtering and sorting 
     */
	$(document).on("click", "ul.pagination li a", function() {
		let agesorting = false;
		let desc = false;
		let selectedSalary = getSeletedSalary();
		if($("#age_sorting"). prop("checked") == true){
			agesorting = true;
		}
		
		if($("#desc_sorting"). prop("checked") == true){
			desc = true;
		}
		
		let val = $(this).text();
		
		// click on the NEXT tag
	  	if(val.toUpperCase()==="NEXT"){
	  		let activeValue = parseInt($("ul.pagination li.active").text());
	  		let totalPages = $("ul.pagination li").length - 2; // -2 beacause 1 for Previous and 1 for Next 
	  		if(activeValue < totalPages){
	  			let currentActive = $("li.active");
	  			fetchCustomers(activeValue, 5, selectedSalary, agesorting, desc); // get next page value
	  			// remove .active class for the old li tag
	  			$("li.active").removeClass("active");
	  			// add .active to next-pagination li
	  			currentActive.next().addClass("active");
	  		}
	  	} else if(val.toUpperCase()==="PREVIOUS"){
	  		let activeValue = parseInt($("ul.pagination li.active").text());
	  		if(activeValue > 1){
	  			// get the previous page
	  			fetchCustomers(activeValue-2, 5, selectedSalary, agesorting, desc);
	  			let currentActive = $("li.active");
	  			currentActive.removeClass("active");
	  			// add .active to previous-pagination li
	  			currentActive.prev().addClass("active");
	  		}
	  	} else {
	  		fetchCustomers(parseInt(val) - 1, 5,  selectedSalary, agesorting, desc);
	  		// add focus to the li tag
	  		$("li.active").removeClass("active");
	  		$(this).parent().addClass("active");
	  	}
	});
});

Integration Testing – Jquery Ajax Pagination Bootstrap Table & Nodejs RestAPI

Testcase 1 – Pagination View

- Start Loading time:

Testcase 1 - Ajax Pagination - Start Loading Time
Testcase 1 - Ajax Pagination - Start Loading Time

– Select page 3:

Testcase 1 – Ajax Nodejs Pagination Example – Page 3
Testcase 1 – Ajax Nodejs Pagination Example – Page 3

Testcase 2 – Pagination and Filtering View

– Pagination and Filtering with salary is $4000:

Testcase 2 - Nodejs Filtering with Salary = $4000
Testcase 2 - Nodejs Filtering with Salary = $4000

– Pagination and Filtering with Salary = $3500:

Testcase 2 - Ajax Filtering with Salary = $3500
Testcase 2 - Ajax Filtering with Salary = $3500

Testcase 3 – Pagination Filtering and Sorting View

– Pagination and Filtering with salary is $3000 and Sorting by Age:

Testcase 3 - Nodejs Ajax Filtering with Salary = 3000 and Sorting by Age
Testcase 3 - Nodejs Ajax Filtering with Salary = 3000 and Sorting by Age

– Pagination and Filtering with salary = $3500 and sorting by Age with Desc direction:

Testcase 3 - Ajax Nodejs Filtering with Salary = 3500 and Sorting by Age with Desc Direction
Testcase 3 - Ajax Nodejs Filtering with Salary = 3500 and Sorting by Age with Desc Direction

Further Reading

Related post:

SpringBoot Tutorial – Ajax Pagination Filtering and Sorting with Bootstrap Table Example – JQuery Ajax Tutorial

Sourcecode

Summary steps to implement the "Ajax Pagination Node.js MySQL RestAPIs Example - Client Side Pagination in Node.js Tutorial":

- How to build Node.js Express Pagination MySql

- Pagination Ajax Javascript with Bootstrap Table – Frontend Development

- Sourcecode:

Nodejs-Pagination-Filtering-Sorting

- Github Sourcecode:

Nodejs-Ajax-MySQL-Pagination-Examples - GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *