Listing the last message of each conversation, involving an user, in MongoDB

Question

Lets Say I have a mongo database of messages that looks like this:

{
  "_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
}

So how can I retrieve the last message from each conversation that the user "wood" may have?

Someone else has already posted a similar question on how to do this in mysql. I am asking how to do this in mongoose.

I will post that for reference incase it helps: Private messaging system. Listing last message of each conversation

Would be great if you could help me out. Thank you for your help. I really appreciate it. I am new to mongoose,mongodb and node.js. I come from a php mysql background.

Would be great if you could post actual code of how to do it so I can try it out and provide feedback. Thanks.

My database schema looks like this:

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 }
});

proof of what kind of people run this site: http://i62.tinypic.com/bbntx.jpg

Leaving this website as you can tell what kind of unfriendly people are running this site. Ill probably ask somewhere else. They don't actually care about helping you. They care about their rules. Your a new person they don't welcome you. Id be welcomed anywhere else not treated like dirt like people do here.

Answer 1

Welcome to Stack overflow. Its a decent question, you posted. Please let me take the privilege to help you in the way i can.

This is an aggregation command which can be run in the mongo shell. Please find the explanation inline.

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"}
    }
}
])

You can run the below on 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);
}
)