Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pagination in nodejs with mysql

In my project I need to query the db with pagination and provide user the functionality to query based on current search result. Something like limit, I am not able to find anything to use with nodejs. My backend is mysql and I am writing a rest api.

like image 863
Dark Coder Avatar asked Feb 29 '16 08:02

Dark Coder


People also ask

Does MySQL support pagination?

The pagination is a very interesting concept in MySQL and can be achieved easily by LIMIT and OFFSET clauses. It is very useful when you want to display the large recordset on multiple pages.

Can I use NodeJS with MySQL?

Once you have MySQL up and running on your computer, you can access it by using Node.js. To access a MySQL database with Node.js, you need a MySQL driver.

How does pagination work in node JS?

Pagination in NodeJS is defined as adding the numbers to identify the sequential number of the pages. In pagination, we used to skip and limit for reducing the size of data in the database when they are very large in numbers.


2 Answers

I taked the solution of @Benito and I tried to make it more clear

var numPerPage = 20;
var skip = (page-1) * numPerPage; 
var limit = skip + ',' + numPerPage; // Here we compute the LIMIT parameter for MySQL query
sql.query('SELECT count(*) as numRows FROM users',function (err, rows, fields) {
    if(err) {
        console.log("error: ", err);
        result(err, null);
    }else{
        var numRows = rows[0].numRows;
        var numPages = Math.ceil(numRows / numPerPage);
        sql.query('SELECT * FROM users LIMIT ' + limit,function (err, rows, fields) {
            if(err) {
                console.log("error: ", err);
                result(err, null);
            }else{
                console.log(rows)
                result(null, rows,numPages);
            }
        });            
    }
});
like image 116
Amirouche Zeggagh Avatar answered Oct 12 '22 12:10

Amirouche Zeggagh


You could try something like that (assuming you use Express 4.x).

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

In this example, query results are set in the results field of the response payload, while pagination metadata is set in the pagination field.

As for the possibility to query based on current search result, you would have to expand a little, because your question is a bit unclear.

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

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

Here is the package.json file for this example:

{
  "name": "stackoverflow-pagination",
  "dependencies": {
    "bluebird": "^3.3.3",
    "body-parser": "^1.15.0",
    "express": "^4.13.4",
    "mysql": "^2.10.2"
  }
}
like image 32
Benito Avatar answered Oct 12 '22 12:10

Benito