Mongodb skip and limit not working properly for pagination

Question

I'm working on a project where huge amount of data (in lacks) being stored in the mongodb collection using mongoose. So instead of using mongoose-aggregate-paginate-v2, I'm using skip and limit for pagination.

But the pagination is not working properly. The data shown in the first page is also being shown in the second page. I think as name suggests skip just skipping the number of records and showing the remaining records in the response.

This is the query I'm using

assets.aggregate(
  [
    { $skip: 1 },
    { $match: { company: "61bf118b87b32c3ccc7a96a6" } },
    { $sort: { createdAt: -1 } },
    { $limit: 10 },
    {
      $lookup: {
        from: "tickets",
        localField: "_id",
        foreignField: "assets",
        as: "ticket_assets",
      },
    },
    {
      $lookup: {
        from: "locations",
        localField: "location",
        foreignField: "_id",
        as: "locationData",
      },
    },
    { $unwind: { path: "$locationData", preserveNullAndEmptyArrays: true } },
    {
      $lookup: {
        from: "stores",
        localField: "sublocation",
        foreignField: "_id",
        as: "sublocation",
      },
    },
    { $unwind: { path: "$sublocation", preserveNullAndEmptyArrays: true } },
    {
      $lookup: {
        from: "user_dropdowns",
        localField: "category",
        foreignField: "_id",
        as: "categoryData",
      },
    },
    { $unwind: { path: "$categoryData", preserveNullAndEmptyArrays: true } },
    {
      $lookup: {
        from: "user_dropdowns",
        localField: "type",
        foreignField: "_id",
        as: "typeData",
      },
    },
    { $unwind: { path: "$typeData", preserveNullAndEmptyArrays: true } },
    {
      $lookup: {
        from: "admin_dropdowns",
        localField: "asset_status",
        foreignField: "_id",
        as: "assetStatusData",
      },
    },
    { $unwind: { path: "$assetStatusData", preserveNullAndEmptyArrays: true } },
    {
      $lookup: {
        from: "user_dropdowns",
        localField: "condition",
        foreignField: "_id",
        as: "assetCondition",
      },
    },
    { $unwind: { path: "$assetCondition", preserveNullAndEmptyArrays: true } },
    {
      $lookup: {
        from: "stores",
        localField: "deployedAt",
        foreignField: "_id",
        as: "deployedStore",
      },
    },
    { $unwind: { path: "$deployedStore", preserveNullAndEmptyArrays: true } },
    {
      $lookup: {
        from: "company_contacts",
        localField: "assignedObj.allottedTo",
        foreignField: "_id",
        as: "assignedEmployee",
      },
    },
    {
      $unwind: { path: "$assignedEmployee", preserveNullAndEmptyArrays: true },
    },
    {
      $lookup: {
        from: "locations",
        localField: "using_location",
        foreignField: "_id",
        as: "assetUsingLocation",
      },
    },
    {
      $unwind: {
        path: "$assetUsingLocation",
        preserveNullAndEmptyArrays: true,
      },
    },
    {
      $project: {
        _id: 1,
        asset_name: 1,
        asset_code: 1,
        status: 1,
        createdAt: 1,
        assetId: 1,
        location: { $ifNull: ["$locationData", null] },
        sublocation: { $ifNull: ["$sublocation", null] },
        category: { $ifNull: ["$categoryData", null] },
        type: { $ifNull: ["$typeData", null] },
        asset_status: { $ifNull: ["$assetStatusData", null] },
        ticketCount: { $size: "$ticket_assets" },
        deployedAt: { $ifNull: ["$deployedStore", null] },
        assignedTo: {
          $ifNull: [
            {
              fullName: "$assignedEmployee.fullName",
              _id: "$assignedEmployee._id",
            },
            null,
          ],
        },
        condition: { $ifNull: ["$assetCondition", null] },
        using_location: { $ifNull: ["$assetUsingLocation", null] },
        predictive_maintenance: 1,
        maintenanceValidationData: 1,
        allocationValidationData: 1,
        purchasedFrom: 1,
        warrantyDue: 1,
        lifeTime: 1,
        purchaseDate: 1,
        asset_price: 1,
      },
    },
  ]
);

And please find the attached images of how the response being shown in the frontend table.

Is there any other alternative to fix the pagination in my code or where I'm making the mistake to get the correct pagination.

Answer 1

As you guessed, $skip skips X documents. It has no way to know how you manage your pages in the front-end, it might be pages of 5/10/25/50 documents.

In the request you make to your API, pass it as parameters something like this: /my/awesome/route/results?page_number=5&page_size=10.

That way, in your endpoint via req.params, you know that you have to $skip 4 pages times 10 results, so $skip: 40.

---

An example for this would be:

Say your URL is: https://my-api.com/data-to-retrieve?page_number=3,page_size=10.

You would do

const myApiRoute = (req, res, next) => {
  // -1 because if you want page two, you want the results from 11 to 20, and not from 21 to 30
  const pageSize = parseInt(req.query.page_size) - 1;
  const pageNumber = parseInt(req.query.page_number);
  assets.aggregate(
  [
    { $skip: pageSize * pageNumber },
    { $match: { company: "61bf118b87b32c3ccc7a96a6" } },
    { $sort: { createdAt: -1 } },
    { $limit: pageSize },
    { [...]
}