How to get last conversations/chats as admins-to-student chat in LMS?

I have a Learning Management System (LMS) where students can ask/chat with admins for each lesson.

As an Admin, I want to get last unseen chats from all lessons sorted from dashboard.

Here’s my Message Model:

{
  studentId: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "Student"
  },
  lessonId: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "Lesson"
  },
  from: {
    type: String,
    enum: ["student", "admin"]
  },
  content: {
    type: String
  },
  seenByAdmin: {
    type: Boolean,
    default: false
  },
  createdAt: {
    type: Date,
    default: Date.now()
  }
}

A you can see I don’t care which admin is replying to the student.

My desired output:

[
{
  lessonId: ObjectID,
  chats: [
    {
       studentId: ObjectID,
       createdAt: Date, /* of last message */
       seenByAdmin: Boolean, /* of last message */
       content: String /* content of last message (optional) */
    }
  ]
},
{
  lessonId: ObjectID,
  chats: [
    {
       studentId: ObjectID,
       createdAt: Date, /* of last message */
       seenByAdmin: Boolean, /* of last message */
       content: String /* content of last message (optional) */
    },
    {
       studentId: ObjectID,
       createdAt: Date, /* of last message */
       seenByAdmin: Boolean, /* of last message */
       content: String /* content of last message (optional) */
    }
  ]
}
]

OR

[
  {
    lessonId: ObjectID,
    studentId: ObjectID,
    createdAt: Date, /* of last message */
    seenByAdmin: Boolean, /* of last message */
    content: String /* content of last message (optional) */
  },
  {
    lessonId: ObjectID,
    studentId: ObjectID,
    createdAt: Date, /* of last message */
    seenByAdmin: Boolean, /* of last message */
    content: String /* content of last message (optional) */
  }
]

I have been stuck on writing a mongodb aggregation/query and I would appreciate any help!

Hi @Amr_Elmohamady ,

I assume that the documents in this “chat” messages looks something similar to:

{
    "studentId": {
        "$oid": "62024e920d57c3db7bf0c3a5"
    },
    "lessonId": {
        "$oid": "62024e920d57c3db7bf0c3a6"
    },
    "from": "student",
    "content": "Hello there",
    "seenByAdmin": false,
    "createdAt": ISODate( "2022-02-08T11:05:54.349Z")
}

If you are using the newly available 5.2 release on Atlas we added a neat operator called $top, to present a groups top items based on a sort. In that case the following aggregation should work

db.messages.aggregate([{$group: {
 _id: '$lessonId',
 messages: {
  $top: {
   output: '$$ROOT',
   sortBy: {
    createdAt: -1
   }
  }
 }
}}, {$replaceRoot: {
 newRoot: '$messages'
}}])

If you are using an earlier version you will need to do a slightly different aggregation:

db.messages.aggregate([{$sort: {
 lessonId: 1,
 createdAt: -1
}}, {$group: {
 _id: {
  lessonId: '$lessonId'
 },
 messages: {
  $first: '$$ROOT'
 }
}}, {$replaceRoot: {
 newRoot: '$messages'
}}])

Thanks
Pavel

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.