Mongoose- Group By And Populate : Get unique list of users a person had been talking to like whatsap-CodePudding

i have been working on a chat app and i need to display the list of the users having conversation with the a user. more like the first screen of the whatsapp where you have the list of all users who texted you.

my messagesSchema is as follows:

from : {type : mongoose.Types.ObjectId, ref : "Users"},
to : {type : mongoose.Types.ObjectId, ref : "Users"},
messageType : String,
messageContent :String,
images : [Object],
poster : Object,
status : String,
date: { type: Date, default: Date.now }

i am using nodejs and i have tried so many agrregations, group by and other mongoose queries but fail

please help me with the query to get all the different unique users a person have been having a conversation with their single last message and populate the 'to' and 'from'.

CodePudding user response：

Data:

[
  {
    "_id": ObjectId("100000000000000000000000"),
    "from": ObjectId("100000000000000000000000"),
    "to": ObjectId("200000000000000000000000"),
    "date": ISODate("2021-09-03T11:23:25.184Z"),
    "messageContent": "111"
  },
  {
    "_id": ObjectId("200000000000000000000000"),
    "from": ObjectId("200000000000000000000000"),
    "to": ObjectId("100000000000000000000000"),
    "date": ISODate("2021-09-02T11:23:25.184Z"),
    "messageContent": "222"
  },
  {
    "_id": ObjectId("300000000000000000000000"),
    "from": ObjectId("300000000000000000000000"),
    "to": ObjectId("100000000000000000000000"),
    "date": ISODate("2021-09-04T11:23:25.184Z"),
    "messageContent": "333"
  },
  {
    "_id": ObjectId("400000000000000000000000"),
    "from": ObjectId("300000000000000000000000"),
    "to": ObjectId("400000000000000000000000"),
    "date": ISODate("2021-09-05T11:23:25.184Z"),
    "messageContent": "444"
  }
]

Aggregate: (you are ObjectId("100000000000000000000000"))

db.collection.aggregate([
  {
    $match: {
      "$or": [
        { "from": ObjectId("100000000000000000000000") },
        { "to": ObjectId("100000000000000000000000") }
      ]
    }
  },
  {
    $project: {
      whom: {
        $first: {
          $filter: {
            input: [ "$from", "$to" ],
            as: "i",
            cond: { $ne: [ "$$i", ObjectId("100000000000000000000000") ]
            }
          }
        }
      },
      date: 1,
      messageContent: 1,
      to: 1,
      from: 1
    }
  },
  {
    $sort: { "date": -1 }
  },
  {
    $group: {
      _id: "$whom",
      date: { "$first": "$date" },
      message: { "$first": "$messageContent" },
      to: { "$first": "$to" },
      from: { "$first": "$from" }
    }
  }
])

Result:

[
  {
    "_id": ObjectId("300000000000000000000000"),
    "date": ISODate("2021-09-04T11:23:25.184Z"),
    "message": "333"
  },
  {
    "_id": ObjectId("200000000000000000000000"),
    "date": ISODate("2021-09-03T11:23:25.184Z"),
    "message": "222"
  }
]

Example: mongoplayground