Server Side Pagination in Node.js Angular 10 + MySQL + Sequelize

Tutorial: Server Side Pagination in Node.js Angular 10 MySQL database + Express + Sequelize CRUD

In the tutorial, I introduce how to build an “Angular 10 Nodejs Pagination RestAPIs Example with MySQL database (Server Side Pagination with filtering and sorting)” project using Express framework and Sequelize crud queries to interact with database’s records.

– Nodejs Express project (server side pagination) produces pagination RestAPIs with MySQL database records using Sequelize CRUD queries.
– Angular 10 project (client side pagination) will consume the Node.js pagination RestAPIs then show up on component’s views.

Overview – Angular 10 Nodejs Pagination RestAPIs Example (Server Side Pagination in Node.js Angular 10)

Architecture Design Angular Node.js Application

In the tutorial “Server Side Pagination in Node.js Angular 10”, We develop 2 projects:

• Backend Project – Nodejs MySQL Pagination Application gets data from MySQL database then provides RestAPIs with pagination, filtering and sorting function for frontend
• Frontend Project – Angular Application use HttpClient to fetch data from Backend Application then shows them in Bootstrap table with pagination, filtering and sorting functions

Goal

– 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:

– Angular Frontend Pagination with Filtering and Sorting table:

Video Guide – Server Side Pagination in Node.js Angular 10

For the tutorial “Angular 10 Nodejs Pagination RestAPIs Example (Server Side Pagination)”, I create a Youtube video guide with clearly steps to debug full-stack for all running flows of living code from Angular client to Nodejs backend pagination:

Server Side Pagination in Node.js in MySQL

Related post:

Nodejs MySQL Pagination Filtering Sorting Tutorial – Stack: Express RestAPIs + Sequelize ORM + MySQL database 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.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:

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:

• 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 buid 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 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/loizenai.git"
  },
  "keywords": [
    "nodejs",
    "filtering",
    "sorting",
    "pagination"
  ],
  "author": "https://loizenai.com",
  "license": "ISC",
  "bugs": {
    "url": "https://github.com/github.com/loizenai/issues"
  },
  "homepage": "https://github.com/github.com/loizenai#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 project with below structure:

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

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));

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');
  
// 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('/', 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');

router.get('/api/customers/pagination', customers.pagination);
router.get('/api/customers/filteringbyage', customers.filteringByAge);
router.get('/api/customers/pagefiltersort', customers.pagingfilteringsorting);

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: 'loizenaidb',
  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:

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://loizenai.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://loizenai.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:

Angular Client Pagination Table Development with Nodejs RestAPI

Related post:

Angular Nodejs Fullstack CRUD Application with MySQL/PostgreSQL – Angular 10-9-8 HttpClient + Nodejs Express, Sequelize ORM

Pagination in Angular Overview

Create Angular Project

We create an Angular Pagination Application with 2 main blocks:

1. CustomerService is used to fetch data through Angular HTTP Client
2. TableComponent is used to display data with pagination, filtering and sorting view

Here is the Angular project structure:

– We implement a component: angulartable with 2 files angulartable.component.html and angular.component.ts to display data on web.
– We implement a service customer.service.ts using Httpclient to interact with SpringBoot restPAPI to fetch data with pagination, filtering and sorting.
– We define 2 helpful class customer.ts and message.ts for mapping data with RestAPIs.

Define Customer.ts class

export class Customer {
    id: number;
    firstname: string;
    lastname: string;
    age: number;
    salary: number;
    address: string;
    copyrightBy: string;
}

Define Message.ts class

import { Customer } from './customer';

export class Message {
    customers: Customer[];
    totalPages: number;
    pageNumber: number;
    pageSize: number;
}

Implement Angular Customer Service

We implement CustomerService service which uses a built-in HttpClient of Angular to create 2 http methods:

• getPagableCustomers() is used to a Page of Customer from SpringBoot RestAPI with 5 params for pagination, filtering and sorting data
• getListSalaries() is used to get a list distinct salaries of Customer

Coding:

import { Injectable } from '@angular/core';
import { HttpClient, HttpErrorResponse, HttpParams } from '@angular/common/http';

import { Observable, throwError  } from 'rxjs';
import { catchError, retry } from 'rxjs/operators';

import { Message } from './message';

@Injectable({
  providedIn: 'root'
})
export class CustomerService {

  private baseUrl = 'http://localhost:8080/api/customers';

  constructor(private http: HttpClient) { }


  /**
   * Retrieve all customer from Backend
   */
  getPagableCustomers(pageNumber: number, 
                        pageSize: number, salary: number,
                        agesorting: boolean, desc: boolean): Observable<Message> {
    // Initialize Params Object
    let params = new HttpParams();

    // Begin assigning parameters
    params = params.append('page', pageNumber.toString());
    params = params.append('size', pageSize.toString());
    params = params.append('salary', salary.toString());
    params = params.append('agesorting', agesorting.toString());
    params = params.append('desc', desc.toString());

    return this.http.get<Message>(`${this.baseUrl}` + `/custom/pageable`, { params: params })
                  .pipe(
                    retry(3),
                    catchError(this.handleError)
                  );
  }

  getListSalaries(): Observable<Array<number>>{
    return this.http.get<Array<number>>(`${this.baseUrl}` + `/salaries`)
                  .pipe(
                    retry(3),
                    catchError(this.handleError)
                  );
  }

  private handleError(error: HttpErrorResponse) {
    if (error.error instanceof ErrorEvent) {
      // A client-side or network error occurred. Handle it accordingly.
      console.error('An error occurred:', error.error.message);
    } else {
      // The backend returned an unsuccessful response code.
      // The response body may contain clues as to what went wrong,
      console.error(
        `Backend returned code ${error.status}, ` +
        `body was: ${error.error}`);
    }
    // return an observable with a user-facing error message
    return throwError(
      'Something bad happened; please try again later.');
  };
}

Implement Angular Table Component

Implement View Page

We use Bootstrap to implement the view page, so we need add it to the head tag of index.html page.
- Coding of index.html

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>AngularHttpclient</title>
  <base href="/">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="icon" type="image/x-icon" href="favicon.ico">
  <!-- Latest compiled and minified CSS -->
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
  <!-- jQuery library -->
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <!-- Popper JS -->
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <!-- Latest compiled JavaScript -->
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
</head>
<body>
  <div class="container" > 
    <div class="col-sm-7" style="background-color: #e6f9ff; margin:10px;padding:10px; border-radius: 5px">
      <div class="alert alert-danger">
          <h3>Angular + SpringBoot Tutorial</h3>
          <h6><strong>Pagination + Filtering and Sorting </strong></h6>
          <hr>
          <p>@Copyright by <strong><span style="color:blue"><a href="https://loizenai.com">https://loizenai.com</a></span></strong><br>
              youtube: <strong><a href="https://www.youtube.com/channel/UChkCKglndLes1hkKBDmwPWA"><span style="color:crimson">loizenai</span></a></strong>
          </p>
      </div>
      <app-root></app-root>
    </div> 
  </div>
</body>
</html>

In the tutorial "Angular 10 Nodejs Pagination RestAPIs Example", we develop table component to do the task with 3 main parts for Angular view page to display data with pagination, filtering and sorting:

• a Bootstrap table with a pagination bar to display data and selecting a specific pagination
• a Select List for distinct salaries to choose a specific salary for doing the pagination with salary filtering
• a checkbox group with 2 fields age and desc to make a decission to sort or NOT with the age field by descending or ascending direction

- Code of angulartable.component.html view page:

<div *ngIf="customers.length">
    <h3>Processing...</h3>
    <br>
    <div>    
      <h5>Do you want sorting by?</h5>
      <form>
        <label class="checkbox-inline">
          <input type="checkbox"
                  [checked]="agesorting"
                  (change)="onAgeSortingChange($event)"> Age
          <br>
          <input  [disabled]="!agesorting" type="checkbox"
                  [checked]="desc"
                  (change)="desc = !desc"> Desc
        </label>
      </form>
      <button type="button" class="btn btn-primary" (click)="sortNow()">Sort Now</button>
    </div>
    <hr>
    <div class="form-group">
      <label for="sel1"><h5>Filtering by Salary:</h5></label>
      <select class="form-control" id="salary_filtering"
                    (change)="getCustomerPagesWithSalaryFiltering($event.target.value)">
        <option>All</option>
        <option *ngFor="let salary of salaries">{{salary}}</option>
      </select>
    </div>
    <h3>Customers</h3>
    <table 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>
            <th></th>
          </tr>
        </thead>
        <tbody>
            <tr *ngFor="let customer of customers">
              <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://loizenai.com">{{customer.copyrightBy}}</a></td>
            </tr>
        </tbody>
    </table>
</div>

<ul class="pagination justify-content-center">
  <li class="page-item"><a (click)="previousClick()" class="page-link">Previous</a></li>
  <li *ngFor='let index of pageIndexes' class="page-item" [ngClass]="active(index)">
          <a (click)="getPaginationWithIndex(index)" class="page-link">
            {{index + 1}}
          </a>
  </li>
  <li class="page-item"><a (click)="nextClick()" class="page-link">Next</a></li>
</ul>

Implement Table Component

We develop Table Component angulartable.component.ts arround below list of variables:

  currentSelectedPage:number = 0;
  totalPages: number = 0;
  customers: Array<Customer> = [];
  pageIndexes: Array<number> = [];

  // salaries list
  salaries: Array<number> = [];
  selectedSalary: number = -1;

  // sorting
  agesorting: boolean = false;
  desc: boolean = false;

What does it mean?

• currentSelectedPage defines the current selected page for getting data
• totalPages is the number page of data for pagination
• customers contains a list customers of current fetching page
• pageIndexes contains an array numbers that presents the number of pages to show for user selecting
• salaries contains a list of salaries with distinct values
• selectedSalary is a selected salary for filtering with pagination
• ageSorting is a boolean variable that determines whether the sorting with age field or not
• desc is a boolean variable that is used to determines a direction for sorting by descending or ascending (the default value)

We use the CustomerService to fetch data from SpringBoot RestAPIs, so we need to inject it in the constructor:

  constructor(private customerService: CustomerService) { 
  }

Show the fetching data with Table and Pagination Bar

For getting data from SpringBoot RestAPI, we define a function getPage() as below:

  getPage(page: number, selectedSalary: number, agesorting: boolean, desc: boolean){

    this.customerService.getPagableCustomers(page, pageSize, selectedSalary, 
                                              agesorting, desc)
            .subscribe(
                (message: Message) => {
                  console.log(message);
                  this.customers = message.customers;
                  this.totalPages = message.totalPages;
                  this.pageIndexes = Array(this.totalPages).fill(0).map((x,i)=>i);
                  this.currentSelectedPage = message.pageNumber;
                },
                (error) => {
                  console.log(error);
                }
            );
  }

At the initial time of Angular loading page, we need invoke the getPage function with appropriate parameters as below code:

  ngOnInit(): void {
    // get the first Page
    this.getPage(0, -1, false, false);
    // get Salaries
    this.getSalaries();
  }

On the view page, for showing the fetching data, we need binding them as below html code using the Angular directive:

<tbody>
    <tr *ngFor="let customer of customers">
      <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://loizenai.com">{{customer.copyrightBy}}</a></td>
    </tr>
</tbody>

Here is the how we build the pagination bar on Html view by binding the fetching data pageIndexes with html element by using a Angular built-in directive *ngFor:

<ul class="pagination justify-content-center">
  <li class="page-item"><a (click)="previousClick()" class="page-link">Previous</a></li>
  <li *ngFor='let index of pageIndexes' class="page-item" [ngClass]="active(index)">
          <a (click)="getPaginationWithIndex(index)" class="page-link">
            {{index + 1}}
          </a>
  </li>
  <li class="page-item"><a (click)="nextClick()" class="page-link">Next</a></li>
</ul>

For processing the navigation with Next and Previous buttons on Pagination bar, we define 2 funtions nextClick() and previousClick() as below:

 nextClick(){
    if(this.currentSelectedPage < this.totalPages-1){
      this.getPage(++this.currentSelectedPage,
                   this.selectedSalary, this.agesorting, this.desc);
    }  
  }

  previousClick(){
    if(this.currentSelectedPage > 0){
      this.getPage(--this.currentSelectedPage,
                   this.selectedSalary, this.agesorting, this.desc);
    }  
  }
}

Do the Pagination and Filtering with salary selected option

Review again the implementation in html view page for the filtering select-option form funtion:

<div class="form-group">
  <label for="sel1"><h5>Filtering by Salary:</h5></label>
  <select class="form-control" id="salary_filtering"
                (change)="getCustomerPagesWithSalaryFiltering($event.target.value)">
    <option>All</option>
    <option *ngFor="let salary of salaries">{{salary}}</option>
  </select>
</div>

We use the Angular built-in directive *ngFor to build the selected-option list of distinct value of salaries.

The salaries list is fetched at the initial time of Angular Component:

  ngOnInit(): void {
    ...
    // get Salaries
    this.getSalaries();
  }

  getSalaries() {
    this.customerService.getListSalaries()
          .subscribe(
            (salaries: Array<number>) => {
              console.log(salaries);
              this.salaries = salaries;
            },
            (error) => {
              console.log(error);
            }
          );
  }

If having any change from selected form, the function (change)="getCustomerPagesWithSalaryFiltering($event.target.value) will be invoked:

getCustomerPagesWithSalaryFiltering(optionValue: any) {
  // convert option string value to appropriate number
  if(optionValue != "All"){
    this.selectedSalary = parseInt(optionValue);
  } else {
    this.selectedSalary = -1;
  }

  // load customer again with filtering and pagination api
  this.getPage(0, this.selectedSalary, this.agesorting, this.desc);
}

Sorting Implementation with Age and Desc checkbox

Review again the html sorting div:

<div>    
  <h5>Do you want sorting by?</h5>
  <form>
    <label class="checkbox-inline">
      <input type="checkbox"
              [checked]="agesorting"
              (change)="onAgeSortingChange($event)"> Age
      <br>
      <input  [disabled]="!agesorting" type="checkbox"
              [checked]="desc"
              (change)="desc = !desc"> Desc
    </label>
  </form>
  <button type="button" class="btn btn-primary" (click)="sortNow()">Sort Now</button>
</div>

If having any changes on the checkboxes, they will immediately affect to the 2 variables agesorting and desc.

//.html file
(change)="onAgeSortingChange($event)"
(change)="desc = !desc"

//.ts file
onAgeSortingChange(value: any){
    this.agesorting = !this.agesorting;
    if(!this.agesorting){
      // reset desc
      this.desc = false;
    }
  }

When pressing the button Sort Now, the function sortNow() will be invoked for re-fetching the pagination and sorting data from SpringBoot RestAPI:

sortNow(){
  if(this.desc == true && this.agesorting == false){
    alert("Please select 'agesorting' option before selecting 'desc' option!");
    return;
  }
  // load again from backend for sorting with age field
  this.getPage(0, this.selectedSalary, this.agesorting, this.desc);
}

angular.component.ts code

import { Component, OnInit, ɵclearResolutionOfComponentResourcesQueue } from '@angular/core';
import { CustomerService } from '../customer.service';
import { Message } from '../message';
import { Customer } from '../customer';

const pageSize:number = 5;

@Component({
  selector: 'app-angulartable',
  templateUrl: './angulartable.component.html'
})
export class AngulartableComponent implements OnInit {
  currentSelectedPage:number = 0;
  totalPages: number = 0;
  customers: Array<Customer> = [];
  pageIndexes: Array<number> = [];

  // salaries list
  salaries: Array<number> = [];
  selectedSalary: number = -1;

  // sorting
  agesorting: boolean = false;
  desc: boolean = false;

  constructor(private customerService: CustomerService) { 
  }

  ngOnInit(): void {
    // get the first Page
    this.getPage(0, -1, false, false);
    // get Salaries
    this.getSalaries();
  }

  getPage(page: number, selectedSalary: number, agesorting: boolean, desc: boolean){

    this.customerService.getPagableCustomers(page, pageSize, selectedSalary, 
                                              agesorting, desc)
            .subscribe(
                (message: Message) => {
                  console.log(message);
                  this.customers = message.customers;
                  this.totalPages = message.totalPages;
                  this.pageIndexes = Array(this.totalPages).fill(0).map((x,i)=>i);
                  this.currentSelectedPage = message.pageNumber;
                },
                (error) => {
                  console.log(error);
                }
            );
  }

  getPaginationWithIndex(index: number) {
    this.getPage(index, this.selectedSalary, this.agesorting, this.desc);
  }

  getSalaries() {
    this.customerService.getListSalaries()
          .subscribe(
            (salaries: Array<number>) => {
              console.log(salaries);
              this.salaries = salaries;
            },
            (error) => {
              console.log(error);
            }
          );
  }

  getCustomerPagesWithSalaryFiltering(optionValue: any) {
    // convert option string value to appropriate number
    if(optionValue != "All"){
      this.selectedSalary = parseInt(optionValue);
    } else {
      this.selectedSalary = -1;
    }

    // load customer again with filtering and pagination api
    this.getPage(0, this.selectedSalary, this.agesorting, this.desc);
  }

  sortNow(){
    if(this.desc == true && this.agesorting == false){
      alert("Please select 'agesorting' option before selecting 'desc' option!");
      return;
    }
    // load again from backend for sorting with age field
    this.getPage(0, this.selectedSalary, this.agesorting, this.desc);
  }

  onAgeSortingChange(value: any){
    this.agesorting = !this.agesorting;
    if(!this.agesorting){
      // reset desc
      this.desc = false;
    }
  }

  active(index: number) {
    if(this.currentSelectedPage == index ){
      return {
        active: true
      };
    }
  }

  nextClick(){
    if(this.currentSelectedPage < this.totalPages-1){
      this.getPage(++this.currentSelectedPage,
                   this.selectedSalary, this.agesorting, this.desc);
    }  
  }

  previousClick(){
    if(this.currentSelectedPage > 0){
      this.getPage(--this.currentSelectedPage,
                   this.selectedSalary, this.agesorting, this.desc);
    }  
  }
}

Integration Testing – Angular Nodejs Pagination

Below is a suite of testcases for the tutorial: "Angular 10 Nodejs Pagination RestAPIs Example (Server Side Pagination)".

Testcase 1 – Angular Pagination View

– Start time:

- Select Page 3:

Testcase 2 – Angular Pagination and Filtering View

– Pagination and Filtering with salary is $4000:

– Pagination and Filtering with Salary = $3500:

Testcase 3 – Angular 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

• Angular Table Material Design
• Nodejs Model Querying

Should Reading:

1. Angular 10 Spring Boot JWT Authentication Example – Angular 6, 8, 9, 10 + Spring Security + MySQL/PostgreSQL
2. Angular & Nodejs JWT Authentication fullstack Example | Angular 6, 7, 8, 9 – Express RestAPIs + JWT + BCryptjs + Sequelize + MySQL/PostgreSQL
3. Django RestAPIs Tutorial – Build Post/Get/Put/Delete request Examples to MySQL/PostgreSQL databases

Sourcecode - Server Side Pagination in Node.js Angular 10

Below are all sourcecodes for the tutorial "Angular 10 Nodejs Pagination RestAPIs Example (Server Side Pagination in Node.js Angular 10)":

- How to buid Node.js Express Mysql Pagination?

• 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

Nodejs-Pagination-Filtering-Sorting

GitHub Sourcecode:

Nodejs-Pagination-Filtering-Sorting - GitHub

- How to build Angular Table Pagination?

• Pagination in Angular Overview
• Create Angular Project
• Define Customer.ts class
• Define Message.ts class
• Implement Angular Customer Service
• Implement Angular Table Component
  • Implement View Page
  • Implement Table Component
    • Show the fetching data with Table and Pagination Bar
    • Do the Pagination and Filtering with salary selected option
    • Sorting Implementation with Age and Desc checkbox
    • angular.component.ts code

- Sourcecode:

AngularTablePagination

- Github sourcecode - Server Side Pagination in Node.js Angular 10:

Angular-Nodejs-MySQL-Pagination - Client Development