I am trying to implement Pagination and Filtering at the backend.

The input to this controller is Page number and Filtering conditions.

Controller:-

const getPosts = asyncHandler(async (req, res) => {
    const {
      page,
      statusFilter,
      typeFilter,
      sourceFilter,
    } = JSON.parse(req.query.filterData);
  
    
  
    var query = [
      {
        $addFields: {

          paramType: typeFilter,
          paramSource: sourceFilter,
          paramStatus: statusFilter,
        },
      },
      {
        $match: {
          $expr: {
            $and: [
              { user: req.user.id },
              {
                $or: [
                  {
                    $eq: ["$paramType", "All"],
                  },
                  {
                    $eq: ["$paramType", "$type"],
                  },
                ],
              },
              {
                $or: [
                  {
                    $eq: ["$paramSource", "All"],
                  },
                  {
                    $eq: ["$paramSource", "$source"],
                  },
                ],
              },
              {
                $or: [
                  {
                    $eq: ["$paramStatus", "All"],
                  },
                  {
                    $eq: ["$paramStatus", "$status"],
                  },
                ],
              },
            ],
          },
        },
      },
      {
        $project: {
          paramSource: false,
          paramType: false,
          paramStatus: false,
        },
      },
    ];
  
    //pagination
    const PAGE_SIZE = 5;
    const PAGE = parseInt(page) || 0;
  
    // aggregate query
    const aggregateQuery = await Post.aggregate([query]);

  
    const total = aggregateQuery.length;
  
  
    const Allposts = await Post.aggregate([query])
      .limit(PAGE_SIZE)
      .skip(PAGE_SIZE * PAGE)
      .sort({ createdAt: -1 });
  
    const totalPages = Math.ceil(total / PAGE_SIZE);
  
  
    res.status(200).json({ totalPages, Allposts });
  });

Problem:-

The pagination and filtering part works as intended but only for the first page, when I go to second page the Allposts object is empty.

Why is the Allposts object empty after first page?

Edit:-

Sample Data:-

{
    "_id" : 1,
    "type" : "Type A",
    "source" : "Source A",
    "status" : "Status A",
    "createdAt" : ISODate("2022-04-13T17:12:28.096Z"),
    "updatedAt" : ISODate("2022-04-13T17:12:28.096Z"),
    "__v" : 0
},
{
    "_id" : 2,
    "type" : "Type B",
    "source" : "Source C",
    "status" : "Status B",
    "createdAt" : ISODate("2022-04-13T17:12:28.096Z"),
    "updatedAt" : ISODate("2022-04-13T17:12:28.096Z"),
    "__v" : 0
},

{
    "_id" : 3,
    "type" : "Type A",
    "source" : "Source A",
    "status" : "Status A",
    "createdAt" : ISODate("2022-04-13T17:12:28.096Z"),
    "updatedAt" : ISODate("2022-04-13T17:12:28.096Z"),
    "__v" : 0
},

{
    "_id" : 4,
    "type" : "Type A",
    "source" : "Source C",
    "status" : "Status B",
    "createdAt" : ISODate("2022-04-13T17:12:28.096Z"),
    "updatedAt" : ISODate("2022-04-13T17:12:28.096Z"),
    "__v" : 0
}

Updated aggreation query:-

var query = [
    
    {
      $addFields: {
        paramType: typeFilter,
        paramSource: sourceFilter,
        paramStatus: statusFilter,
      },
    },
    {
      $match: {
        $expr: {
          $and: [
            { user: req.user.id },
            {
              $or: [
                {
                  $eq: ["$paramType", "All"],
                },
                {
                  $eq: ["$paramType", "$type"],
                },
              ],
            },
            {
              $or: [
                {
                  $eq: ["$paramSource", "All"],
                },
                {
                  $eq: ["$paramSource", "$source"],
                },
              ],
            },
            {
              $or: [
                {
                  $eq: ["$paramStatus", "All"],
                },
                {
                  $eq: ["$paramStatus", "$status"],
                },
              ],
            },
            
          ],
        },
      },
    },{ $sort : { createdAt : -1 } },
    {
      $project: {
        paramSource: false,
        paramType: false,
        paramStatus: false,
        createdAt : 1,
      },
    }
  ];

CodePudding user response：

If you want pagination total count with one query, you can do something like this:

db.collection.aggregate([
  {
    $addFields: {
      paramType: "typeFilter",
      paramSource: "sourceFilter",
      paramStatus: "statusFilter"
    }
  },
  {
    "$match": {
      // complete here
    }
  },
  {
    $setWindowFields: {
      output: {totalCount: {$count: {}}}}
  },
  {$sort: {createdAt: -1}},
  {$skip: PAGE_SIZE * PAGE},
  {$limit: PAGE_SIZE},
  {
    $facet: {
      results: [
        {
          $project: {
            // here put whatever you want to send to FE
            type: 1,
            source: 1,
          }
        }
      ],
      totalCount: [
        {$limit: 1},
        {$project: {totalCount: 1, _id: 0}}
      ]
    }
  }
])

As you can see on the playground

The $setWindowFields allows you to add the total count to all documents. $sort, $skip and $limit allow the pagination. The $facet allows you to get different outputs from the same documents.