Get last conversations as admin in mongodb

Question

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 by createdAt & seenByAdmin 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!

user_14327540 · Answer

The aggregation pipeline:

{
      $sort: {
        seenByAdmin: 1,
        createdAt: -1
      }
    },
    {
      $group: {
        _id: "$studentId",
        messages: {
          $first: "$$ROOT"
        }
      }
    },
    {
      $replaceRoot: {
        newRoot: "$messages"
      }
    },
    {
      $skip: skip || 0
    },
    {
      $limit: limit || 10
    },
    {
      $lookup: {
        from: "students",
        localField: "studentId",
        foreignField: "_id",
        as: "student"
      }
    },
    {
      $project: {
        studentId: 1,
        lessonId: 1,
        from: 1,
        content: { $substr: ["$content", 0, 150] },
        createdAt: 1,
        seenByAdmin: 1,
        student: {
          $arrayElemAt: ["$student", 0]
        }
      }
    },
    {
      $project: {
        studentId: 1,
        lessonId: 1,
        from: 1,
        content: 1,
        createdAt: 1,
        seenByAdmin: 1,
        student: {
          _id: 1,
          name: 1
        }
      }
    }

Get last conversations as admin in mongodb

1 Answers