Pagination in nodejs with mysql

Question

In my project, I need to request db paginated and provide the user with the functionality to query based on the current search result. Something like a limit, I can't find anything that can be used with nodejs. My backend is mysql and I am writing a rest api.

Answer 1

You can try something like this (assuming you are using Express 4.x).

Use the GET parameters (here the page is the number of desired page results, and npp is the number of results per page).

results , pagination pagination.

, , .

var express = require('express');
var mysql   = require('mysql');
var Promise = require('bluebird');
var bodyParser = require('body-parser');
var app = express();

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'myuser',
  password : 'mypassword',
  database : 'wordpress_test'
});
var queryAsync = Promise.promisify(connection.query.bind(connection));
connection.connect();

// do something when app is closing
// see http://stackoverflow.com/questions/14031763/doing-a-cleanup-action-just-before-node-js-exits
process.stdin.resume()
process.on('exit', exitHandler.bind(null, { shutdownDb: true } ));

var bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: true }));

app.get('/', function (req, res) {
  var numRows;
  var queryPagination;
  var numPerPage = parseInt(req.query.npp, 10) || 1;
  var page = parseInt(req.query.page, 10) || 0;
  var numPages;
  var skip = page * numPerPage;
  // Here we compute the LIMIT parameter for MySQL query
  var limit = skip + ',' + numPerPage;
  queryAsync('SELECT count(*) as numRows FROM wp_posts')
  .then(function(results) {
    numRows = results[0].numRows;
    numPages = Math.ceil(numRows / numPerPage);
    console.log('number of pages:', numPages);
  })
  .then(() => queryAsync('SELECT * FROM wp_posts ORDER BY ID DESC LIMIT ' + limit))
  .then(function(results) {
    var responsePayload = {
      results: results
    };
    if (page < numPages) {
      responsePayload.pagination = {
        current: page,
        perPage: numPerPage,
        previous: page > 0 ? page - 1 : undefined,
        next: page < numPages - 1 ? page + 1 : undefined
      }
    }
    else responsePayload.pagination = {
      err: 'queried page ' + page + ' is >= to maximum page number ' + numPages
    }
    res.json(responsePayload);
  })
  .catch(function(err) {
    console.error(err);
    res.json({ err: err });
  });
});

app.listen(3000, function () {
  console.log('Example app listening on port 3000!');
});

function exitHandler(options, err) {
  if (options.shutdownDb) {
    console.log('shutdown mysql connection');
    connection.end();
  }
  if (err) console.log(err.stack);
  if (options.exit) process.exit();
}

package.json :

{
  "name": "stackoverflow-pagination",
  "dependencies": {
    "bluebird": "^3.3.3",
    "body-parser": "^1.15.0",
    "express": "^4.13.4",
    "mysql": "^2.10.2"
  }
}

Answer 2

, , , , .

router.get('/items/:page',(req,res) => {
const db = require('mysql'),
        Pagination = require('./pagination'),

        // Get current page from url (request parameter)
        page_id = parseInt(req.params.page),
        currentPage = page_id > 0 ? page_id : currentPage,

//Change pageUri to your page url without the 'page' query string 
        pageUri = '/items/';

        /*Get total items*/
        db.query('SELECT COUNT(id) as totalCount FROM items',(err,result)=>{

            // Display 10 items per page
            const perPage = 10,
                totalCount = result[0].totalCount;

            // Instantiate Pagination class
            const Paginate = new Pagination(totalCount,currentPage,pageUri,perPage);


            /*Query items*/
            db.query('SELECT * FROM items LIMIT '+Paginate.perPage+' OFFSET '+Paginate.start,(err,result)=>{

                data = {
                    items : result,
                    pages : Paginate.links()
                }

                // Send data to view
                res.render('items',data);
            });
        });

});

""

{{ pages }}

pagination.js >> pagination.js ,

class Pagination{

constructor(totalCount,currentPage,pageUri,perPage=2){
    this.perPage = perPage;
    this.totalCount =parseInt(totalCount);
    this.currentPage = parseInt(currentPage);
    this.previousPage = this.currentPage - 1;
    this.nextPage = this.currentPage + 1;
    this.pageCount = Math.ceil(this.totalCount / this.perPage);
    this.pageUri = pageUri;
    this.offset  = this.currentPage > 1 ? this.previousPage * this.perPage : 0;
    this.sidePages = 4;
    this.pages = false;
}



links(){
    this.pages='<ul class="pagination pagination-md">';

    if(this.previousPage > 0)
        this.pages+='<li class="page-item"><a class="page-link" href="'+this.pageUri + this.previousPage+'">Previous</a></li>';


        /*Add back links*/
        if(this.currentPage > 1){
            for (var x = this.currentPage - this.sidePages; x < this.currentPage; x++) {
                if(x > 0)
                    this.pages+='<li class="page-item"><a class="page-link" href="'+this.pageUri+x+'">'+x+'</a></li>';
            }
        }

        /*Show current page*/
        this.pages+='<li class="page-item active"><a class="page-link" href="'+this.pageUri+this.currentPage+'">'+this.currentPage+'</a></li>';

        /*Add more links*/
        for(x = this.nextPage; x <= this.pageCount; x++){

            this.pages+='<li class="page-item"><a class="page-link" href="'+this.pageUri+x+'">'+x+' </a></li>';

            if(x >= this.currentPage + this.sidePages)
                break;
        }


        /*Display next buttton navigation*/
        if(this.currentPage + 1 <= this.pageCount)
            this.pages+='<li class="page-item"><a class="page-link" href="'+this.pageUri+this.nextPage+'">Next</a></li>';

        this.pages+='</ul>';

    return this.pages;
}
}
module.exports = Pagination;

Answer 3

I was looking for a quick solution. Maybe it will be useful for someone.

SELECT id  FROM complexCoding LIMIT ? OFFSET ?
",req.query.perpage,((req.query.page-1) * req.query.perpage)

Remember to paginate pages by page total count of iddivided