Find last message in coversation

Question

Find last message in coversation

I have 2 schemas:

var UserSchema   = new Schema({
    login: {type:String, unique: true, trim:true, index:true},
    password: String
)};

var MessageSchema   = new Schema({
    text: String,
    from_id: {type:mongoose.Schema.ObjectId, ref: 'user', index:true},
    to_id: {type:mongoose.Schema.ObjectId, ref: 'user', index:true},
    date: Number
});

Now for example I have 4 users in the database. Assuming user1 sent 5 messages to each other user, user2 and user3 each replied to user1 message. Now I need to return only one recent message from each dialog. How can i do this?

node.js
mongodb
mongoose
mongodb-query

Answer 1

One excellent way to do this would be by using MongoDB's aggregation framework.

I'm not 100% clear on your specific use-case, but I would think that using $unwind to get all of the messages in the same place, and then filtering by perhaps a $setUnion for uniqueness, then a $sort and a $limit if you want it to only display the most recent messages.

Here's a link to the quick reference: http://docs.mongodb.org/manual/meta/aggregation-quick-reference/

Hope this helps, and good luck!

Answer 2

Everything done here is mongo console you will have to translate to your driver yourself

The easy way

If you want to query for specific conversations add a conversation_id to your message (maybe the usernames combined or just a random string) then you can aggregate with

db.messages.aggregate({$sort:{date:-1}},
                      {$group:{_id:"$conversation_id", message:{$first:"$$ROOT"}})

Or just embed you messages in a conversation document like David K. seems to have thought you where already doing.

The "I want this to work" way

I build an aggregate that does what you want but you may want to go with the easy way after you see this (maybe someone can think of a shorter one).

First the collection modeled after your schema:

> db.chat.find().pretty()
{       "from" : 1,
        "to" : 2,
        "text" : "First from 1 to 2",
        "date" : 1}
{       "from" : 2,
        "to" : 1,
        "text" : "First answer from 2 to 1",
        "date" : 2}
{       "from" : 1,
        "to" : 2,
        "text" : "Second from 1 to 2",
        "date" : 3}
{       "from" : 1,
        "to" : 3,
        "text" : "First 1 to 3",
        "date" : 3}
{       "from" : 3,
        "to" : 1,
        "text" : "Reply 3 to 1",
        "date" : 4}

_id stipped for readbility and as you see two conversations, one between users 1 and 2 and one between 1 and 3.

Now the aggregate query:

db.chat.aggregate({$project: {to:1,from:1,date:1,text:1,duplicator:{$const: [0,1]}}},
                  {$unwind:"$duplicator"},
                  {$group:{_id:"$_id",
                         parts:{$push:{$cond:[{$eq:"$duplicator",0]},"$from","$to"]}},
                         messages:{$addToSet: {text:"$text",date:"$date"}}}},
                  {$unwind:"$parts"},
                  {$sort:{parts:1}},
                  {$group:{_id:"$_id",
                           parts:{$push:"$parts"},
                           messages:{$first:"$messages"}}},
                  {$sort:{"messages.date":-1}},
                  {$group:{_id:"$parts", last_message:{$first:"$messages"}}})

Output:

{  "_id" : [1,2],
   "last_message" : [{ "text" : "Second from 1 to 2",
                       "date" : 3 }]
}
{  "_id" : [1,3],
        "last_message" : [{"text" : "Reply 3 to 1",
                           "date" : 4}]
}

The last two parts ($sort and $group) are the same as in the easy way. The rest is to build a conversation_id named parts for participants. This is done by

Adding a new array duplicator
Unwinding that array to get a duplicate of each message
Pushing to and from in an array parts for each message. parts will work a conversation_id

Those steps above are inspired by this answer

Then again unwinding parts
to $sort them because [1,2] != [2,1]
and $group them into one array again

Tose steps result in a conversation id which can then be used like proposed in the two line aggreate above.

The lesson

Build you schema according to the way you want to query it because raping mongodb with strange aggregates is only fun until it kicks you in the balls in self defense...

The "you formulated your question wrong"

If you just want the last message of a specific conversation you could use

db.chat.find({to:{$in:[ user1,user2]},from:{$in:[user1,user2]}})
            .sort({date:-1})
            .limit(1)