MongoDB - distinct with multiple fields and get most recent result

Question

I am saving message communication between users in mongoDB in format below

instance = {
    'from_user' : from_user,
    'to_user' : to_user,
    'message' : msg_text,
    'date' : timezone.now(),
}

For logged-in user I need to query his communication with all others and fetch only their single most recent message text. e.g. from following dataset

{'from_user':'user1', 'to_user':'user2', 'message':'Hello World'}
{'from_user':'user2', 'to_user':'user1', 'message':'Hello World2'} # most recent between user1 & user2
{'from_user':'user1', 'to_user':'user3', 'message':'Hello World3'}
{'from_user':'user3', 'to_user':'user4', 'message':'Hello World4'}

query result should be following

{'from_user':'user2', 'to_user':'user1', 'message':'Hello World2'}
{'from_user':'user1', 'to_user':'user3', 'message':'Hello World3'}

http://stackoverflow.com/questions/26725658/listing-the-last-message-of-each-conversation-involving-an-user-in-mongodb/26747130#26747130 - Have a look at this. — BatScream, Dec 03 '14 at 18:12

score 0 · Answer 1 · answered Dec 03 '14 at 15:53

The following query finds the most recent message to or from user1:

db.msgs.find({ "$or" : [ { "from_user" : "user1" }, { "to_user" : "user1" } ] })
       .sort({ "date" : -1 })
       .limit(1)

If you want to find the most recent message to a user and the most recent message from a user, you'll need two queries:

db.msgs.find({ "from_user" : "user1" }).sort({ "date" : -1 }).limit(1)
db.msgs.find({ "to_user" : "user1" }).sort({ "date" : -1 }).limit(1)

MongoDB - distinct with multiple fields and get most recent result

1 Answers1