mongodb - 如何根据相关集合属性过滤的相关集合计数对集合(如前 20 名)进行排序？

Question

我有以下架构:

var postSchema = new Schema({
    autor:              {type: Schema.Types.ObjectId, ref: 'user', required: true},
    texto:              {type: String},
    likes:              [{type: Schema.Types.ObjectId, ref: 'like'}],

});

var likeSchema = new Schema({
    user:       {type: Schema.Types.ObjectId, ref: 'user', required: true},
    post:       {type: Schema.Types.ObjectId, ref: 'post', required: true},
    _created_at:{type: Date},
});

我希望集合“post”中的所有文档使用 attr“_created_at”按过去 24 小时(可能是其他时间段)内创建的关系“likes”的计数排序。

F.I. “过去 24 小时内获得最多点赞的帖子”

我听说使用聚合是个好主意，但我缺乏这方面的经验，也不知道我应该选择哪种管道。

Answer 1

如果帖子ID对你来说足够了，你可以使用:

db.like.aggregate([
    //filter the likes creation dates as you need
    { $match  : { _created_at: { $gte: ISODate("2000-01-01T00:00:00.000Z"), $lt: ISODate("2020-12-31T00:00:00.000Z") } } },
    //group by post ID and count them
    { $group  : { _id: "$post", count: {$sum: 1} } },
    //sort by count, descending
    { $sort   : { count : -1 } },
    //limit the results in 20 maximum (if you need only the top 20)
    { $limit  : 20 }
])

这将返回如下列表:

[{
    "_id" : ObjectId("5774826af4a48761f2ff0da1"),
    "count" : 5
}, ...]

但是，如果您需要在同一个查询中获取完整的帖子，您将需要 MongoDB v.3.2($lookup 在此之前不可用)。查询将是:

db.like.aggregate([
    //filter the likes creation dates as you need
    { $match  : { _created_at: { $gte: ISODate("2000-01-01T00:00:00.000Z"), $lt: ISODate("2020-12-31T00:00:00.000Z") } } },
    //group by post ID and count them
    { $group  : { _id: "$post", count: {$sum: 1} } },
    //sort by count, descending
    { $sort   : { count : -1 } },
    //limit the results in 20 maximum (if you need only the top 20)
    { $limit  : 20 },
    //bring the complete post from the related collection
    { $lookup : { from: "post", localField: "_id", foreignField: "_id", as: "post" } },
    //deconstructs the array created by lookup to a single object
    { $unwind : "$post" },
    //remove the ID and include on the complete post and count (this step is optional)
    { $project: { _id: 0, post: true, count: true } }
])

这将返回如下列表:

[{
    "count" : 5,
    "post" : {
        "_id" : ObjectId("5774826af4a48761f2ff0da1"),
        "autor" : ObjectId("577480bcf4a48761f2ff0d92"),
        "texto" : "texto06",
        "likes" : [
            ObjectId("5774882df4a48761f2ff0db9"),
            ObjectId("5774882df4a48761f2ff0dba"),
            ObjectId("5774882df4a48761f2ff0dbb"),
            ObjectId("5774882df4a48761f2ff0dbc"),
            ObjectId("5774882df4a48761f2ff0dbd")
        ]
    }
}, ...]

引用资料:

https://docs.mongodb.com/manual/reference/method/db.collection.aggregate/

https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

https://docs.mongodb.com/manual/reference/method/db.collection.count/

希望这对您有所帮助!

干杯!