How can I get a unique list of conversations?

I basically 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": "jon",
  "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
}


How can I search the database to give me "unique" records based on the "to" and "from" fields?

So that I have results returned 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": "john",
  "message": "hi nas how are you?",
  "time": new Date(1415053492125),
  "__v": 0
}


So it should check the "to" field and "from" field for duplicates. I call these duplicates they are the exact same results just reversed:
To: nas From: dan message: some message
To: dan From: nas message: some message

To nas from John message: some message
To john from nas message: some message

So it should return 1 row for each of those:
To: nas From: dan message: some message
To nas from John message: some message


it should also consider which of those events took place latest.

So it should return just the latest row. So it can probably use the _id field to work out which one of those is the last.


What am I looking to do with this code:

Well basically this is what I want to use it for:

https://az545221.vo.msecnd.net/skype-faq-media/faq_content/skype/screenshots/fa12285/fa12285_k_android_recent_events.png

As you can see in the image the way skype and whats app have a recent conversations list I want to create something similar. I just want to pull recent conversations for a specific user.

So I should just be able to say search for recent conversations for Dan and it should pull up all records the user has had interactions with.


I have come asked this question other places a few weeks back and I havent been able to find any good working solutions. 

All help will be appreciated. thank you so much.


I come from a php mysql background and I am finding this very hard to do in mongoose.


I am using mongoose and node.js.

This is what my message schema looks like:

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

Would be great if someone can help me out. Thank you.



You can use aggregation framework to use $project to convert to and from to arrays, then create a union of them using $setUnion and then group on them.



Thanks. I am aware of the aggregation framework. I did try something but couldn't get it to work properly. An actual code example would be really nice.



This is something similar someone else is asking how to do it in mysql:

Just to give you an idea of what im looking for:
http://stackoverflow.com/questions/12160243/private-messaging-system-listing-last-message-of-each-conversation



Someone asked a similar question on stack over flow about how to do something similar in mysql. Maybe it will give you a better understanding of what im trying to do:

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