Sequelize pagination

Question

Using sequelize on my nodejs web app, I want to query posts using pagination (by date). Reading sequelize docs, they offer to use offset and limit.

Since I want to display the posts from new to old, I need to consider the date they were created. For example, if I limit the first query to 10 page, and before executing the second query a new post was created, the next query with offset of 10 will result a duplicate post from the last query.

How should I implement the pagination so it will support new entries?

score 60 · Answer 1 · edited Jun 25 '21 at 02:52

60

The easiest way to do this is to use Sequelize's findAndCountAll

Post.findAndCountAll({
    where: {...},
    order: [...],
    limit: 5,
    offset: 0,
}).then(function (result) {
    res.render(...);
});

Here, result has both the result of your query and count as result.rows and result.count. You can then increment the offset and use this for pagination.

Sequelize documentation for findAndCountAll

edited Jun 25 '21 at 02:52

Caleb Syring

1,107
1
11
20

answered Jan 05 '17 at 20:47

0X1A

798
1
7
7

There's also a `findAndCount` helper method which will return the count of items based off this but not the items themselves. – Zach Bloomquist Apr 06 '18 at 23:07
2

Also, you don't need to do `findAndCountAll` if you only want to do pagination - `findAll` also supports the `limit` and `offset` parameters. – Zach Bloomquist Apr 06 '18 at 23:17
@ZachBloomquist , but how would you know how many pages you have? – Ioanna Apr 09 '20 at 09:43
@Ioanna see the linked docs - `res.result` will contain the total number of matching rows, while `res.rows` will contain the rows limited by `limit` and `offset` – Zach Bloomquist Apr 09 '20 at 14:35
5

Yes, if you use `findAndCountAll`. But in your comment you said `findAll` could also be used for pagination. Indeed, `findAll` also accepts a `limit` and an `offset` params, i'm not sure these are enough for pagination. Wouldn't you also need the `rows` that only `findAndCountAll` provides? – Ioanna Apr 13 '20 at 04:51
`findAll` is not sufficient because you need the total number of items in order to calculate the total number of pages. So you need the `findAndCountAll` and send both the `.rows` and the `.count`. – ajbraus Apr 23 '22 at 00:54

score 21 · Answer 2 · answered Jun 05 '20 at 13:07

Try this:

const paginate = (query, { page, pageSize }) => {
  const offset = page * pageSize;
  const limit = pageSize;

  return {
    ...query,
    offset,
    limit,
  };
};


model.findAll(
  paginate(
    {
      where: {}, // conditions
    },
    { page, pageSize },
  ),
);

In order to avoid boilerplate code

score 17 · Answer 3 · edited Feb 26 '23 at 01:43

If you want to have a stable pagination, don't paginate on row offset, since it's volatile, for the reason you mention.

You should aim for paginating on a value that is stable over time and use a where clause for filtering results. The best case would be if you have an auto-incrementing id, but the post date could also be reasonable.

Something like:

Post.findAll({
  where: {
    createdDate: {
      $lt: previousDate,
    },
  },
  limit: 10,
});

You need to keep track of previousDate for this ofc. This approach also has some caveats, and you may need to combine it with client-side de-duplication.

Here is a blog post that probably has all the answers you need: Pagination: You're (Probably) Doing It Wrong

What if I want to sort posts by its likes and paginate, can't use date in that case right? — Luka Samkharadze, Jul 25 '23 at 18:04

score 15 · Answer 4 · edited Feb 26 '23 at 01:42

15

With findAndCountAll here count is useful for pagination, from this total count we can limit as we want and also with async and await

let resAccidents = await ModalName.findAndCountAll({
  where: { createdByID: employeeID },
  offset: 0,
  limit: 10,
});

this will return a count of total records as per where condition and 1st 10 records of it, then increase the value of offset to fetch further records.

edited Feb 26 '23 at 01:42

Ilyas karim

4,592
4
33
47

answered Mar 04 '19 at 11:37

akshay bagade

1,169
1
11
24

1

For retrieve all just set offset: null, limit: null – Rohit Parte Dec 24 '19 at 06:23
1

Does this actually count all the documents or does it use count estimate internally? – itaintme May 29 '20 at 05:34
This should be recommeded use while using datatables at the frontend (Y) – Arfeen Mar 22 '23 at 18:40

score 10 · Answer 5 · edited Feb 26 '23 at 01:44

You can simply do that

let limit = 10;
let offset = 0 + (req.body.page - 1) * limit;
Posts.findAndCountAll({
  offset: offset,
  limit: limit,
  order: [["date", "ASC"]],
})
  .then(async (result) => {
    return res.status(200).json({
      status: true,
      message: res.__("success"),
      innerData: result,
    });
  })
  .catch((err) => {
    return validator.InvalidResponse(res, `${err}`);
  });

score 3 · Answer 6 · edited Feb 03 '21 at 12:34

3

Try this instead:

db.findAll({
   offset: page_no,// your page number
      limit:25,// your limit

edited Feb 03 '21 at 12:34

Tomerikoo

18,379
16
47
61

answered May 27 '19 at 11:24

sarathlal

43
4

findAndCountAll method will help to count total data count – sarathlal May 27 '19 at 11:26
This also helpful if we don't want total count. – Rohit Parte Dec 24 '19 at 05:44

Arjun G · Answer 7 · 2021-02-12T08:18:16.310

This one solved my issue.

export const paginate = (query, schema) => {
  let page = query.page ? query.page - 1 : 0;
  page = page < 0 ? 0 : page;
  let limit = parseInt(query.limit || 10);
  limit = limit < 0 ? 10 : limit;
  const offset = page * limit;
  const where = {};
  delete query.page;
  delete query.limit;
  Object.keys(schema).forEach((key) => {
    schema[key] && query[key] ? (where[key] = query[key]) : null;
  });
  return {
    where: where,
    offset,
    limit,
  };
};

@Get()
findAll(@Query() query): unknown {
  return this.model.findAll(paginate(query, {xx:1}));
}

/model?xx=yy&page=1&limit=5

score -1 · Answer 8 · answered Oct 04 '19 at 05:36

var defered = Q.defer();
const offset = queryString.offset * queryString.limit;
const limit = queryString.limit;
var queryWhere = { class_id: { $ne: null }, section_id: { $ne: null } };
var searchClass = {};
var searchSection = {};
if (queryString) {
    if (queryString.class && queryString.class !== "") {
       searchClass = { class_id: { $eq: queryString.class } };
    } else if (queryString.class && queryString.class === "") {
       searchClass = { class_id: { $ne: null } };
    }

if (queryString.section && queryString.section !== "") {
      searchSection = { section_id: { $eq: queryString.section } };
} else if (queryString.section && queryString.section === "") {
      searchSection = { section_id: { $ne: null } };
}
}

queryWhere = {
    $and: [[searchClass], [searchSection]]
};
const schoolDB = require("../../db/models/tenant")(schema);
const Student = schoolDB.model("Student");
Student.findAll({
   attributes: [
  "id",
  "first_name",
  "last_name",
  "profile_image_url",
  "roll_number",
  "emergency_contact_number"
],
offset: offset,
limit: limit,
where: queryWhere,
order: [["roll_number", "ASC"]]
})
.then(result => {
  defered.resolve(result);
})
.catch(err => {
  defered.reject(err);
});

Recommended using Sequelize's own operators

var defered = Q.defer();
const offset = queryString.offset * queryString.limit;
const limit = queryString.limit;
var queryWhere = { class_id: { $ne: null }, section_id: { $ne: null } };
var searchClass = {};
var searchSection = {};
if (queryString) {
    if (queryString.class && queryString.class !== "") {
       searchClass = { class_id: { $eq: queryString.class } };
    } else if (queryString.class && queryString.class === "") {
       searchClass = { class_id: { $ne: null } };
    }

if (queryString.section && queryString.section !== "") {
      searchSection = { section_id: { $eq: queryString.section } };
} else if (queryString.section && queryString.section === "") {
      searchSection = { section_id: { $ne: null } };
}
}

queryWhere = {
    $and: [[searchClass], [searchSection]]
};
const schoolDB = require("../../db/models/tenant")(schema);
const Student = schoolDB.model("Student");
Student.findAll({
   attributes: [
  "id",
  "first_name",
  "last_name",
  "profile_image_url",
  "roll_number",
  "emergency_contact_number"
],
offset: offset,
limit: limit,
where: queryWhere,
order: [["roll_number", "ASC"]]
})
.then(result => {
  defered.resolve(result);
})
.catch(err => {
  defered.reject(err);
});

Try adding a description of what you are doing and why. – coler-j Jan 30 '20 at 16:29 — coler-j, Jan 30 '20 at 16:29

Sequelize pagination

8 Answers8

Linked