node.js - 在 MongoDB 中列出每个对话的最后一条消息，涉及一个用户

Question

假设我有一个如下所示的 mongo 消息数据库:

{
  "_id": ObjectId("5458009c1ab2354c029d7178"),
  "to": "dan",
  "from": "wood",
  "message": "hi dan how are you?",
  "time": new Date(1415053468590),
  "__v": 0
}
{
  "_id": ObjectId("545800b45eaf364d026c1cba"),
  "to": "wood",
  "from": "dan",
  "message": "hi wood how are you?",
  "time": new Date(1415053492125),
  "__v": 0
}
{
  "_id": ObjectId("5458009c1ab2354c029d7178"),
  "to": "billy",
  "from": "wood",
  "message": "hi billy how are you?",
  "time": new Date(1415053468590),
  "__v": 0
}
{
  "_id": ObjectId("545800b45eaf364d026c1cba"),
  "to": "wood",
  "from": "billy",
  "message": "hi wood how are you?",
  "time": new Date(1415053492125),
  "__v": 0
}

那么我如何才能从用户“wood”可能进行的每个对话中检索最后一条消息？

其他人已经发布了关于如何在 mysql 中执行此操作的类似问题。我在问如何在 Mongoose 中做到这一点。

我会将其发布以供引用，以防有帮助: Private messaging system. Listing last message of each conversation

如果你能帮助我，那就太好了。谢谢您的帮助。对此，我真的非常感激。我是 mongoose、mongodb 和 node.js 的新手。我来自php mysql背景。

如果您能发布实际操作代码，我可以尝试并提供反馈，那就太好了。谢谢。

我的数据库架构如下所示:

var messageSchema = new Schema({
to: { type: String, required: true},
from: { type: String, required: true},
message: { type: String, required: true},
time : { type : Date, default: Date.now }
});

证明什么样的人经营这个网站: http://i62.tinypic.com/bbntx.jpg

离开这个网站，你可以知道什么样的不友好的人在运行这个网站。应该会问别的地方。他们实际上并不关心帮助你。他们关心他们的规则。你是一个新人，他们不欢迎你。我会在其他地方受到欢迎，而不像这里的人那样被视为肮脏。

Answer 1

欢迎来到堆栈溢出。这是一个不错的问题，你发布了。请让我有幸尽我所能帮助您。

这是一个可以在 mongo shell 中运行的聚合命令。请找到内联的解释。

db.collection.aggregate([
//match all those records which involve Wood.
{$match:{$or:[{"to":"wood"},{"from":"wood"}]}},
// sort all the messages by descending order
{$sort:{time:-1}},
{
    // Now we need to group messages together, based on the to and from field.
    // We generate a key - "last_message_between", with the value being a concatenation
    // result of the values in to and from field.
    // Now, Messages from Wood to billy and Billy to wood should be treated under a single group right.
    // To achieve that, we do a small trick to make the result contain the name coming last
    // alphabetically, first. So our key for all the Messages from Wood to Billy and Billy to Wood would be 
    // "Wood and Billy".
    // And then we just display the first document that appears in the group, that would be the 
    // latest Message.
    $group:{"_id":{
    "last_message_between":{
        $cond:[
            {
                $gt:[
                {$substr:["$to",0,1]},
                {$substr:["$from",0,1]}]
            },
            {$concat:["$to"," and ","$from"]},
            {$concat:["$from"," and ","$to"]}
        ]
    }
    },"message":{$first:"$$ROOT"}
    }
}
])

您可以在 Mongoose 上运行以下代码。

Collection.aggregate(
{$match:{$or:[{"to":"wood"},{"from":"wood"}]}},
{$sort:{time:-1}},
{
    $group:{"_id":{
    "last_message_between":{
        $cond:[
            {
                $gt:[
                {$substr:["$to",0,1]},
                {$substr:["$from",0,1]}]
            },
            {$concat:["$to"," and ","$from"]},
            {$concat:["$from"," and ","$to"]}
        ]
    }
    },"message":{$first:"$$ROOT"}
    }
},
function(err, res)
{
    if (err) return handleError(err);
    console.log(res);
}
)