List of last post of each user related conversation in MongoDB

Question

Lets say that I have a mongo message database 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 get the last message from every conversation that a user tree can have?

Someone else posted a similar question on how to do this in mysql. I ask how to do this in mongoose.

I will post that for help it helps: Private messaging system. Display the last message about each conversation

It would be great if you could help me. Thanks for the help. I really appreciate this. I am new to mongoose, mongodb and node.js. I come from the php mysql background.

It would be great if you could post the actual code on how to do this so that I can try and provide feedback. Thanks.

My database schema is as follows:

 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 which people run this site: http://i62.tinypic.com/bbntx.jpg

Leaving this site, as you can tell, what unfriendly people use this site. I probably ask somewhere else. They really do not care about helping you. They care about their rules. Your new person whom they do not welcome. Id be welcomed in any other place not considered as dirt, like the people here.

Answer 1

Welcome to stack overflow. His worthy question, you sent. Please allow me the privilege to help you as much as I can.

This is an aggregation command that can be run in the mongo shell. Please find an 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 below on the 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); } )