Mongoose limit offset and count query

在使用Mongoose进行分页查询时，通常需要两个参数：page（当前页码）和limit（每页显示的记录数）。页码和记录数可以让我们计算出需要跳过多少条记录来获取当前页的数据。

这里有一个基础的例子，演示了怎样实现分页：

假设我们有一个模型 Item，我们需要实现一个查询来获取第 page 页的数据，每页显示 limit 条记录。

javascript
const page = req.query.page; // 从请求中获取页码
const limit = req.query.limit; // 从请求中获取每页显示的记录数

const options = {
  page: parseInt(page, 10) || 1, // 设置默认页码为1
  limit: parseInt(limit, 10) || 10, // 设置默认每页显示记录数为10
};

// 计算跳过多少条记录
const skip = (options.page - 1) * options.limit;

// 分页查询
Item.find()
  .skip(skip)
  .limit(options.limit)
  .exec((err, items) => {
    if (err) {
      // 处理错误
    } else {
      // 处理查询结果
    }
  });

在上面的代码中，我们首先从请求中获取页码和每页记录数。然后，我们计算出需要跳过的记录数，这是通过将当前页码减去1，然后乘以每页的记录数来实现的。这个值就是 .skip() 方法需要的参数。.limit() 方法则告诉Mongoose每页需要获取的记录数。

这样，每次调用这个分页查询，你只需要传递相应的 page 和 limit 参数即可。如果用户没有提供这些参数，我们会使用默认值。

值得注意的是，这种方法适用于数据量不是非常大时的分页。当处理大量数据时，每次都用 .skip() 和 .limit() 方法可能会导致性能问题，因为 .skip() 实际上是通过读取并跳过前面的记录来实现的，这在记录数非常多的时候可能很慢。解决这个问题的一种方法是使用类似于 "无限滚动" 的策略，根据最后加载的记录的ID来获取下一批记录。

I suggest you to use 2 queries:

1. db.collection.count() will return total number of items. This value is stored somewhere in Mongo and it is not calculated.

2. db.collection.find().skip(20).limit(10) here I assume you could use a sort by some field, so do not forget to add an index on this field. This query will be fast too.

I think that you shouldn't query all items and than perform skip and take, cause later when you have big data you will have problems with data transferring and processing.

Instead of using 2 separate queries, you can use aggregate() in a single query:

Aggregate "$facet" can be fetch more quickly, the Total Count and the Data with skip & limit

shell
    db.collection.aggregate([

      //{$sort: {...}}

      //{$match:{...}}

      {$facet:{

        "stage1" : [ {"$group": {_id:null, count:{$sum:1}}} ],

        "stage2" : [ { "$skip": 0}, {"$limit": 2} ]
  
      }},
     
     {$unwind: "$stage1"},
  
      //output projection
     {$project:{
        count: "$stage1.count",
        data: "$stage2"
     }}

 ]);

output as follows:-

shell
[{
     count: 50,
     data: [
        {...},
        {...}
      ]
 }]

Also, have a look at https://docs.mongodb.com/manual/reference/operator/aggregation/facet/

db.collection_name.aggregate([ { '$match' : { } }, { '$sort' : { '_id' : -1 } }, { '$facet' : { metadata: [ { $count: "total" } ], data: [ { $skip: 1 }, { $limit: 10 },{ '$project' : {"_id":0} } ] // add projection here wish you re-shape the docs } } ] )

Instead of using two queries to find the total count and skip the matched record.
$facet is the best and optimized way.

1. Match the record

2. Find total_count

3. skip the record

4. And also can reshape data according to our needs in the query.

There is a library that will do all of this for you, check out mongoose-paginate-v2

You don't have to use two queries or one complicated query with aggregate and such.

You can use one query

example:

shell
const getNames = async (queryParams) => {

  const cursor = db.collection.find(queryParams).skip(20).limit(10);
  return {
    count: await cursor.count(),
    data: await cursor.toArray()
  }
  
}

mongo returns a cursor that has predefined functions such as count, which will return the full count of the queried results regardless of skip and limit

So in count property, you will get the full length of the collection and in data, you will get just the chunk with offset of 20 and limit of 10 documents