mongoose how can I get the last message for each conversation

user_15751269 0
Viewed 266

I have the following schema for messages and I want to query all conversation of a user.

I want to use same scheme for conversations list and messages.(I tried to separate them but it did not work.) All I want to do is to be able to fetch chat list.

The last message and the one you are messaging with.

const messengerScheme = mongoose.Schema({ 
    users:[{
        type: mongoose.Schema.Types.ObjectId,
        ref:'User'
    }],
    from:{
        type: mongoose.Schema.Types.ObjectId, 
        ref: 'User'
    },
    message:{
        type:String,
    },
    hasImage:{
        type:Boolean,
    },
    image:{
        type:String
    }
})

Suppose two users has a conversation like the following:

/* 1 */
{
    "_id" : ObjectId("609ee8c7593cda1de475cfff"),
    "users" : [ 
        ObjectId("60841c75b6c2a522047d1988"), 
        ObjectId("6084036ad4d4cd40a47afba4")
    ],
    "from" : ObjectId("60841c75b6c2a522047d1988"),
    "message" : "Hiiii",
    "hasImage" : false,
    "createdAt" : ISODate("2021-05-14T21:16:55.318Z"),
    "updatedAt" : ISODate("2021-05-14T21:16:55.318Z"),
    "__v" : 0
}

/* 2 */
{
    "_id" : ObjectId("609ee8e5593cda1de475d000"),
    "users" : [ 
        ObjectId("6084036ad4d4cd40a47afba4"), 
        ObjectId("60841c75b6c2a522047d1988")
    ],
    "from" : ObjectId("6084036ad4d4cd40a47afba4"),
    "message" : "hiiii !",
    "hasImage" : false,
    "createdAt" : ISODate("2021-05-14T21:17:25.723Z"),
    "updatedAt" : ISODate("2021-05-14T21:17:25.723Z"),
    "__v" : 0
}

/* 3 */
{
    "_id" : ObjectId("609ee8ec593cda1de475d001"),
    "users" : [ 
        ObjectId("6084036ad4d4cd40a47afba4"), 
        ObjectId("60841c75b6c2a522047d1988")
    ],
    "from" : ObjectId("6084036ad4d4cd40a47afba4"),
    "message" : "how are you ?",
    "hasImage" : false,
    "createdAt" : ISODate("2021-05-14T21:17:32.686Z"),
    "updatedAt" : ISODate("2021-05-14T21:17:32.686Z"),
    "__v" : 0
}

/* 4 */
{
    "_id" : ObjectId("609ee90b593cda1de475d002"),
    "users" : [ 
        ObjectId("60841c75b6c2a522047d1988"), 
        ObjectId("6084036ad4d4cd40a47afba4")
    ],
    "from" : ObjectId("60841c75b6c2a522047d1988"),
    "message" : "I am pretty fine, what about you ?",
    "hasImage" : false,
    "createdAt" : ISODate("2021-05-14T21:18:03.224Z"),
    "updatedAt" : ISODate("2021-05-14T21:18:03.224Z"),
    "__v" : 0
}

When I query with a user ID "6084036ad4d4cd40a47afba4", I just want to fetch the last message and the user profile populated

mongoose aggregation-framework node.js mongodb
1 Answers
  • $match user id with users
  • $sort by updatedAt in descending order
  • $limit 1 message in result
db.collection.aggregate([
  { $match: { users: ObjectId("6084036ad4d4cd40a47afba4") } },
  { $sort: { updatedAt: -1 } },
  { $limit: 1 }
])

Playground

Conversation wise latest single message:

  • $match user id with users
  • $sort by updatedAt in descending order
  • $setUnion to sort users in a order
  • $group by above sorted users and get first root document
db.collection.aggregate([
  { $match: { users: ObjectId("6084036ad4d4cd40a47afba4") } },
  { $sort: { updatedAt: -1 } },
  {
    $group: {
      _id: { $setUnion: "$users" },
      message: { $first: "$$ROOT" }
    }
  }
])

Playground

user_89871280
Related