在Mongoose中对嵌套的子文档进行排序和分组

I have a schema, Comment, like the one below. It's a system of "comments" and "replies", but each comment and reply has multiple versions. When a user wants to view a comment, I want to return just the most recent version with the status of APPROVED.

我有一个架构,评论,如下所示。这是一个“评论”和“回复”系统,但每个评论和回复都有多个版本。当用户想要查看评论时,我想返回状态为APPROVED的最新版本。

const Version = new mongoose.Schema({
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User'
  },
  body: String,
  created: Date,
  title: String,
  status: {
    type: String,
    enum: [ 'APPROVED', 'OPEN', 'CLOSED' ]
  }
})

const Reply = new mongoose.Schema({
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User'
  },
  created: Date,
  versions: [ Version ]
})

const Comment = new mongoose.Schema({
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User'
  },
  created: Date,
  versions: [ Version ],
  replies: [ Reply ]
})

I've gotten the parent Comment to display how I want with the code below. However, I've had trouble applying that to the sub-document, Reply.

我已经获得了父评论以显示我想要的代码如下。但是,我很难将其应用于子文档,回复。

const requestedComment = yield Comment.aggregate([
  { $match: {
    query
  } },
  { $project: {
    user: 1,
    replies: 1,
    versions: {
      $filter: {
        input: '$versions',
        as: 'version',
        cond: { $eq: [ '$$version.status', 'APPROVED' ] }
      }
    },
  }},
  { "$unwind": "$versions" },
  { $sort: { 'versions.created': -1 } },
  { $group: {
    _id: '$_id',
    body: { $first: '$versions.body' },
    title: { $first: '$versions.title' },
    replies: { $first: '$replies' }
  }}
])
.exec()

Any help achieving the same result with the replies subdocuments would be appreciated. I would like to return the most recent APPROVED version of each reply in a form like this:

任何帮助实现与回复子文档相同的结果将不胜感激。我想以这样的形式返回每个回复的最新APPROVED版本:

comment: {
  body: "The comment's body.",
  user: ObjectId(...),
  replies: [
    {
      body: "The reply's body."
      user: ObjectId(...)
    }
  ]
}

1 个解决方案

#1

Basically you just need to continue the same process on from the existing pipeline. But this time to $unwind out the "versions" per each "replies" entry and $sort them there.

基本上,您只需要从现有管道继续执行相同的过程。但是这次要解除每个“回复”条目中的“版本”并在那里对它们进行排序。

So these are "additional" stages to your pipeline.

所以这些是您管道的“额外”阶段。

// Unwind replies
{ "$unwind": "$replies" },
// Unwind inner versions
{ "$unwind": "$replies.versions" },

// Filter for only approved
{ "$match": { "replies.versions.status": "APPROVED" } },

// Sort on all "keys" and then the "version" date
{ "$sort": { 
    "_id": 1,
    "replies._id": 1,
    "replies.versions.created": -1
}},

// Group replies to get the latest version of each
{ "$group": {
    "_id": {
        "_id": "$_id",
        "body": "$body",
        "title": "$title",
        "replyId": "$replies._id",
        "replyUser": "$replies.user",
        "replyCreated": "$replies.created"
    },
    "version": { "$first": "$replies.version" }
}},

// Push replies back into an array in the main document
{ "$group": {
    "_id": "$_id._id",
    "body": { "$first": "$_id.body" },
    "title": { "$first": "$_id.title" },
    "replies": {
        "$push": {
            "_id": "$_id.replyId",
            "user": "$_id.replyUser" },
            "created": "$_id.replyCreated",       // <-- Value from Reply
            "body": "$version.body",              // <-- Value from specific Version
            "title": "$version.title"
        }
    }
}}

All depending of course on which fields you want, being either from ther Reply or from the Version.

所有这些都取决于您想要的字段,无论是来自答复还是来自版本。

Whichever fields, since you "un-wound" two arrays, you $group back "twice".

无论哪个领域,既然你“解开”了两个阵列,你就可以“回归”两次。

• Once to get the $first items after sorting per Reply

  每次回复排序后获得$ first项目

• Once more to re-construct the "replies" array using $push

  再次使用$ push重新构建“回复”数组

That's all there is too it.

这就是它的全部。

If you were still looking at ways to "sort" the array "in-place" without using $unwind, well MongoDB just does not do that yet.

如果您仍在寻找在不使用$ unwind的情况下“就地”对数组进行“排序”的方法,那么MongoDB还没有这样做。

---

Bit of advice on your design

As a note, I see where you are going with this and this is the wrong model for the type of usage that you want.

作为一个注释,我看到你要去哪里,这是你想要的使用类型的错误模型。

It makes little sense to store "revision history" within the embdedded structure. You are rarely going to use it in general update and query operations, and as this demonstrates, most of the time you just want the "latest".

在嵌入式结构中存储“修订历史”是没有意义的。您很少会在常规更新和查询操作中使用它,并且正如此所示,大多数情况下您只需要“最新”。

So just do that instead, and store a "flag" indicating "revisions" if really necessary. That data can then be stored external to the main structure, and you won't have to jump through these hoops just to get the "latest accepted version" on every request.

所以,只需这样做,并存储一个“标志”,表示“修订”,如果真的有必要。然后,该数据可以存储在主结构的外部,您不必为了获得每个请求的“最新接受版本”而跳过这些环节。