猫鼬限制/偏移量和计数查询

在查询性能方面有点奇怪... 我需要运行一个查询来计算文档的总数，并且还可以返回一个可以被限制和偏移的结果集。

因此，我总共有57个文档，用户希望10个文档被20个文档抵消。

我可以想到两种方法来做到这一点，首先是查询所有57个文档(以数组的形式返回) ，然后使用 array.slice 返回它们想要的文档。第二个选项是运行2个查询，第一个使用 mongo 的本机“ count”方法，然后使用 mongo 的本机 $limit 和 $略过聚合器运行第二个查询。

您认为哪种扩展方式更好? 是在一个查询中完成所有操作，还是运行两个独立的查询？

编辑:

// 1 query
var limit = 10;
var offset = 20;


Animals.find({}, function (err, animals) {
if (err) {
return next(err);
}


res.send({count: animals.length, animals: animals.slice(offset, limit + offset)});
});




// 2 queries
Animals.find({}, {limit:10, skip:20} function (err, animals) {
if (err) {
return next(err);
}


Animals.count({}, function (err, count) {
if (err) {
return next(err);
}


res.send({count: count, animals: animals});
});
});
143748 次浏览
小开
最佳答案

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.

小开

After having to tackle this issue myself, I would like to build upon user854301's answer.

Mongoose ^4.13.8 I was able to use a function called toConstructor() which allowed me to avoid building the query multiple times when filters are applied. I know this function is available in older versions too but you'll have to check the Mongoose docs to confirm this.

The following uses Bluebird promises:

let schema = Query.find({ name: 'bloggs', age: { $gt: 30 } });


// save the query as a 'template'
let query = schema.toConstructor();


return Promise.join(
schema.count().exec(),
query().limit(limit).skip(skip).exec(),


function (total, data) {
return { data: data, total: total }
}
);

Now the count query will return the total records it matched and the data returned will be a subset of the total records.

Please note the () around query() which constructs the query.

小开

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

    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:-

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

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

小开

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

小开 
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.
小开

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

You can use one query

example:

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

小开

Thanks Igor Igeto Mitkovski, a best solution is using native connection

document is here: https://docs.mongodb.com/manual/reference/method/cursor.count/#mongodb-method-cursor.count and mongoose dont support it ( https://github.com/Automattic/mongoose/issues/3283 )

we have to use native connection.

const query = StudentModel.collection.find(
{
age: 13
},
{
projection:{ _id:0 }
}
).sort({ time: -1 })
const count = await query.count()
const records = await query.skip(20)
.limit(10).toArray()

提交答案