Pagination in NodeJS API

Question

How do I do pagination in NodeJS API. I have the code here that has the Post.Find().populate stuffs and I am not sure at which point of the code I can do pagination using limit and offset. And by the way, if I want to do an infinite scroll, should I use limit/offset or limit/skip? Any help greatly appreciated and many thanks in advance.

router.get('/allpost',JWTAuthenticatToken, async (req, res) => {  
    try{
        const post = await Post.find().populate("postedby","_id displayname profileimage").populate("reviewers.postedby","_id displayname")
        res.json(post)
    }catch(error){
        res.json({message:error})
    }
})

I think I can go by this link for the answer. https://stackoverflow.com/questions/5539955/how-to-paginate-with-mongoose-in-node-js/14822142 — Nat, Dec 07 '20 at 05:32

score 2 · Answer 1 · answered Dec 07 '20 at 07:36

For pagination you need values of two things 1.current_page_no 2.item_per_page

then you can code like that,

router.get('/allpost',JWTAuthenticatToken, async (req, res) => {  
    try{
        const post = await Post.find().populate("postedby","_id displayname profileimage").populate("reviewers.postedby","_id displayname").skip((item_per_page * current_page_no)-item_per_page)).limit(item_per_page)

        res.json(post)
    }catch(error){
        res.json({message:error})
    }
})

Thanks. Another method of achieving it – Nat Dec 07 '20 at 12:44 — Nat, Dec 07 '20 at 12:44

score 2 · Accepted Answer · answered Dec 07 '20 at 08:52

I can share with you how my implementation works by using ExpressJS and MySQL database. The ideas behind are:

Determine the numberOfRows of the data that you wish to display form the database.
Set a pageSize to show the number of items to be displayed in single page.
Always return the page number for the current view using req.query, else by default, it will be page 1.
By using the returned page, we can calculate the number of rows of data to be skip.
Lastly, setup the limit(offset, length) in your query.

The coding part will be:

var connection;
var pageSize = 20;
var numberOfRows, numberOfPages;
var numberPerPage = parseInt(pageSize,10) || 1;
var page = parseInt(pageInfo.page, 10) || 1;
var skip = (page - 1) * numberPerPage;
var limit = `${skip} , ${numberPerPage}`;

return connectionPool.getConnection()
.then((connect) => {
  connection = connect;
  return connection.query(`SELECT COUNT(*) AS total ....`); //To get total rows
})
.then(([rows, field]) => {
  numberOfRows = rows[0].total;
  numberOfPages = Math.ceil(numberOfRows / numberPerPage);
  return connection.query(`SELECT .... LIMIT ${limit}`); //Get specific data with LIMIT
})
.then(([rows, field]) => {
  result = {
    rows: rows,
    pagination: {
    current: page,
    numberPerPage: numberPerPage,
    has_previous: page > 1,
    previous: page - 1,
    has_next: page < numberOfPages,
    next: page + 1,
    last_page: Math.ceil(numberOfRows / pageSize)
    }
  }
  return result;
})
.catch((err) => {
  throw new Error(err.message);
})
.finally(() => {
  connection.release();
})

It's a workable code, all you need is just properly organize your returned result correctly in your pagination at the frontend. Although it is done by using MySQL, but I hope you get the concept behind.

thanks and greatly appreciated it – Nat Dec 07 '20 at 12:43 — Nat, Dec 07 '20 at 12:43

score 1 · Answer 3 · answered Dec 07 '20 at 07:42

For pagination you will always need to send the current page. You can either send it as an query string, param or just POST variable.

router.get('/allpost',JWTAuthenticatToken, async (req, res) => {  
    try{
        let page = req.body.page ?? 1;
        let limit = 10;
        const post = await Post.find().populate("postedby","_id displayname profileimage").populate("reviewers.postedby","_id displayname").skip((page * limit) - limit).limit(limit)
        res.json(post)
    }catch(error){
        res.json({message:error})
    }
})

I send it here with an POST.

What you do here you set an limit how much you maximum wanna display. Then you need some basic calculations. On the first page 1 you wanna skip 0, on page 2 you wanna skip 10 on page 3 you wanna skip 20 and so on...

10 * 1 = skips 10. That means on page 1 you will skip 10. But you want to skip 0. For this you need to subtract by 10.

Thanks. I use post instead of get as Axios does not support req.body. — Nat, Dec 07 '20 at 12:45
@Nat well that sentence actually does not make sense because POST variables are in the request body `req.body`. In this exmaple i used POST and not GET — bill.gates, Dec 07 '20 at 12:47
hahaha. Yes indeed, I reread it and I find it strange also. Thanks — Nat, Dec 07 '20 at 14:33

Pagination in NodeJS API

3 Answers3