[mongoose] How can I get a recent conversations list?

To give you an idea of what I am trying to do please take a look at this image:
https://az545221.vo.msecnd.net/skype-faq-media/faq_content/skype/screenshots/fa12285/fa12285_k_android_recent_events.png

I am trying to write a back end for something which is similar to the recent conversations thing that you see on skype on ios or skype on android. Its also the same in whats app.

So I want to be able to pull a list of recent conversations / interactions.

I have a database of messages that look like this:
{
  "_id": ObjectId("5458009c1ab2354c029d7178"),
  "to": "dan",
  "from": "nas",
  "message": "hi dan how are you?",
  "time": new Date(1415053468590),
  "__v": 0
}
{
  "_id": ObjectId("545800b45eaf364d026c1cba"),
  "to": "nas",
  "from": "dan",
  "message": "hi nas how are you?",
  "time": new Date(1415053492125),
  "__v": 0
}

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


So there could be hundreds of records like this, lots of duplicates and lots or reverse duplicates example:
to nas from dan message something
to nas from dan message something
to dan from nas message something
to dan from nas message something
to nas from john message something
to john from nas message something

as you can see lots of duplicates and lots of reverse duplicates.

I only want 1 row per conversation so i have a list of recent conversations like so:
to nas from dan message something
to john from nas message something

Obviously it should take into consideration which of those happened latest and only show the latest row per conversation.

For example if someone sent me a message last it should select that row. if I was the last to send someone a message it should select that row.

So for example if you sent someone a message on skype it will show the persons name you sent the last message to and it will show the message you sent him.
But if that changes... suppose now all of a sudden he sends you a message then its showing the message he sent you. so its selecting the latest row out of those cross duplicates.


Hopefully you understand what I mean its very hard to explain.

I want to have a function where I just pass in the current users username and it just pulls up all his recent conversations.


I cannot find any easy way of doing this... hopefully someone can help me out. 

I come from a php mysql background so I am very new to node.js, mongoose and mongodb.

Would be great if you could help me out. thank you so much.

If you post code please make sure its something I can actually put into my code and test out.


My mongoose model 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 }
});


I really appreciate all help. Thank you



http://stackoverflow.com/questions/12160243/private-messaging-system-listing-last-message-of-each-conversation

An example of something similar being done i mysql



I am new to all this too so I don't know if this is a good idea or if it works.

I think you want to use the user id/objectid instead of string names, two people can have the same name or the name is changed at a later date, etc...causes problems. Perhaps add another field that is an array of the users involved, even if it's just 2 always. With that field indexed, when you query for the users conversations/threads with others, it'll return all the messages that user participated in. You could create a compound index between this new field and your time field(-1 for descending order/latest), but I'm not sure if it'd help. I think the next step would be to use aggregation which should group all the messages to their own threads and then use the $max operator on time field, this should return results with the last message from each thread.

Alternative might be to have a sub-document field where you can store/update the last message of a thread to each user involved when writing the message to database, then you can just access that sub-document from the application user to get what you want. Less processing I think but duplicates data(1 for each user(2) and 1 for message collection/model), could get around that by only storing the objectID to the message and using mongoose Populate() maybe, but this uses extra query for each message you need to populate.



Although if you're really set on this schema approach, you can use $or:

`Messages.find({ $or: [{ from: 'nas', to: 'john' }, { from: 'john', to: 'nas' }] }).sort({ time: -1 }).limit(1).exec(function(error, messages) {});`