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.
- Overview Ajax Pagination in Node.js MySQL RestAPIs Example + Filtering & Sorting
- Video Guide
- Server Side Pagination in Node.js in MySQL
- Pagination Ajax Javascript with Bootstrap Table – Frontend Development
- Integration Testing – Jquery Ajax Pagination Bootstrap Table & Nodejs RestAPI
- Sourcecode
Overview Ajax Pagination in Node.js MySQL RestAPIs Example + Filtering & Sorting
Architecture Design Ajax Node.js Application
– 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 pagesize: 5 – size of a pagesalary: 4000 – filtering bysalaryfieldagesorting: true – sorting by agedesc: true – descending or ascending sorting
– Result:
– Ajax Pagination in Nodejs – Client View:
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:
- 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.jsfile.
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:
.findAll()– Search for multiple elements in the database.findAndCountAll() - Search for multiple elements in the database, returns both data and total count
How about limit & offset for nodejs pagination?
limitis the maximum number of records to fetchoffsetis 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:
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).
Using a Native SQL query to double check the above results:
– 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:
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:
countis the total number records matching the where clause and other filters due to associationsrows– 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:
- Setup Nodejs Project
- Create an Express WEB Application Server
- Define All RestAPI URLs in
router.js - Configure MySQL Database with Sequelize ORM
- Implement All RESTAPIs in
controllers.jsfile
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
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:
- Group 1: Nodejs RestAPIs coding
- file
db.config.jsis used to define MySQL database configuration with Sequelize ORM - file
customer.model.jsis used to define a Sequelize model mapped with corresponding MySQL database table schema. - file
router.jsis used to define all Express RestAPI URLs - file
controller.jsis used to implement detail logic code to process each incoming request - file
server.jsis used to implement a Nodejs Web server
- Group 2: JQuery Ajax and HTML Pagination Client
view/index.html: using Bootstrap and Html to structure the client pagination viewresources/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 sequelizeFor 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
For handling Nodejs RestAPIs' processing that been defined in router.js file, we implement all 3 working functions in controller.js file:
create = (req, res)function is used to handle a GET request at the endpoint/api/customers/createto post a new Customer entity to MySQL databaseexports.getSalaries = (req, res)function is used to handle a GET request at the endpoint/api/customers/getSalariesand returns a list of distinct salaries from databasepagingfilteringsorting = (req, res)function is used to handle a GET request at the endpoint/api/customers/pagefiltersortto do an association quering operation: pagination, plus filtering bysalaryand sorting byage
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
salaryfield:where: {salary: salary} - Order clause - using to sort rows by 2 fileds
agewith 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,pagefor pagination andsalaryfor filtering andagesorting&descfor 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-catchstatement.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 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:
Pagination Ajax Javascript with Bootstrap Table – Frontend Development
Review HTML Pagination Page
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
agefield or Not and sorting withdescending 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 byagefielddesc= 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 requestingsize: a size of a data page for requestingsalary: a selected salary for filteringagesorting: a flag to determine to do the sorting byagefield or notdesc: a flag to determine the direction for sorting,desc= true for descending sorting direction anddesc= 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:
– Select page 3:
Testcase 2 – Pagination and Filtering View
– Pagination and Filtering with salary is $4000:
– Pagination and Filtering with Salary = $3500:
Testcase 3 – Pagination Filtering and Sorting View
– Pagination and Filtering with salary is $3000 and Sorting by Age:
– Pagination and Filtering with salary = $3500 and sorting by Age with Desc direction:
Further Reading
Related post:
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
- Create Nodejs Project
- Create Express Application Server
- Define All RestAPI URLs in router.js
- Configure MySQL Database with Sequelize ORM
- Implement All RESTAPIs in controllers.js file
- Nodejs Sequelize Pagination RestAPIs
- Nodejs GET all Distinct Salary RestAPIs
- Pagination Ajax Javascript with Bootstrap Table – Frontend Development
- Review HTML Pagination Page
- HTML Bootstrap Pagination Table Implementation
- Jquery Ajax Pagination JSON
- Sourcecode:
Nodejs-Pagination-Filtering-Sorting
- Github Sourcecode:
