Get last document from userchat for each user - mongoose

user_15287803 0
Viewed 87

User Schema

const userSchema = new Schema({
  name: { type: String, required: true },
  phoneno: { type: String },
  email: { type: String },
  password: { type: String },
  profilePic: { type: String },
  socketId: { type: String },
});
module.exports = mongoose.model("User", userSchema);

UserChat Schema

const userChatSchema = new Schema({
  message: { type: String, required: true },
  sender: { type: Schema.Types.ObjectId, required: true, ref: "User" },
  receiver: { type: Schema.Types.ObjectId, required: true, ref: "User" },
  time: { type: Date, required: true },
});
module.exports = mongoose.model("UserChat", userChatSchema);

I need a query to fetch only last userchat of currently logged in user with all other users i.e. a chat where currently logged in user can be sender or receiver and each of all other users can also be sender or receiver and that chat must be latest chat between logged in user and other user.

User and UserChat collections

e.g. if currently logged in user is ObjectId("6039c2513b3fd51f3595d82f"), then query must give following output:

[
  {
    _id: ObjectId("6039c2393b3fd51f3595d82e"),
    name: "userONE",
    phoneno: "123456",
    latestMsg: {
      _id: ObjectId("6039c2df3b3fd51f3595d833"),
      message: "from userONE to userTWO 1",
      receiver: ObjectId("6039c2513b3fd51f3595d82f"), // logged in user
      sender: ObjectId("6039c2393b3fd51f3595d82e"),
      time: ISODate("2021-02-27T03:56:15.122Z"),
    },
  },
  {
    _id: ObjectId("6039c25c3b3fd51f3595d830"),
    name: "userTHREE",
    phoneno: "12345678",
    latestMsg: {
      _id: ObjectId("6039c60ea8061225286e57e4"),
      message: "from userTWO to userTHREE 4",
      receiver: ObjectId("6039c25c3b3fd51f3595d830"),
      sender: ObjectId("6039c2513b3fd51f3595d82f"), // logged in user
      time: ISODate("2021-02-27T04:09:50.579Z"),
    },
  },
  {
    _id: ObjectId("6039c2673b3fd51f3595d831"),
    name: "userFOUR",
    phoneno: "123456789",
    latestMsg: {
      _id: ObjectId("6039c5f2a8061225286e57e2"),
      message: "from userFOUR to userTWO 3",
      receiver: ObjectId("6039c2513b3fd51f3595d82f"), // logged in user
      sender: ObjectId("6039c2673b3fd51f3595d831"),
      time: ISODate("2021-02-27T04:09:22.283Z"),
    },
  },
]
mongoose aggregation-framework mongodb-query node.js mongodb
1 Answers

Scenario of the answer:

  1. Total three users.
  2. Prakash1, Prakash2 have chatted with Dheemanth twice.
  3. Dheemanth has responded to Prakash1 so Dheemanth's response is the last message shown.
  4. Dheemanth has not responded to Prakash2 so Prakash2's last message is shown.

Query:

db.users.aggregate([
    {
        $match: {
            _id: ObjectId("6039437ce0c7d52970d3f9f3") // Loged in user _id
        }
    },
    {
        $lookup: {
            from: "chats",
            let: { user_id: "$_id" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $or: [
                                { $eq: ["$receiver", "$$user_id"] },
                                { $eq: ["$sender", "$$user_id"] }
                            ]
                        }
                    }
                },
                {
                    $sort: { time: -1 }
                },
                {
                    $group: {
                        _id: {
                            $concat: [
                                { $toString: { $min: ["$sender", "$receiver"] } },
                                { $toString: { $max: ["$sender", "$receiver"] } }
                            ]
                        },
                        sender: { $first: "$sender" },
                        receiver: { $first: "$receiver" },
                        message: { $first: "$message" },
                        time: { $first: "$time" }
                    }
                }
            ],
            as: "latestMsg"
        }
    },
    { $unwind: "$latestMsg" }
]);

Output: for Dheemanth

/* 1 createdAt:2/27/2021, 12:22:44 AM*/
{
    "_id" : ObjectId("6039437ce0c7d52970d3f9f3"),
    "name" : "Dheemanth",
    "latestMsg" : {
        "_id" : "6039437ce0c7d52970d3f9f36039437ce0c7d52970d3f9f5",
        "sender" : ObjectId("6039437ce0c7d52970d3f9f5"),
        "receiver" : ObjectId("6039437ce0c7d52970d3f9f3"),
        "message" : "Can u help me?",
        "time" : ISODate("2021-02-27T00:38:00.000+05:30")
    }
},

/* 2 createdAt:2/27/2021, 12:22:44 AM*/
{
    "_id" : ObjectId("6039437ce0c7d52970d3f9f3"),
    "name" : "Dheemanth",
    "latestMsg" : {
        "_id" : "6039437ce0c7d52970d3f9f36039437ce0c7d52970d3f9f6",
        "sender" : ObjectId("6039437ce0c7d52970d3f9f3"),
        "receiver" : ObjectId("6039437ce0c7d52970d3f9f6"),
        "message" : "Ok paste your query.",
        "time" : ISODate("2021-02-27T00:41:00.000+05:30")
    }
}

Output: for Prakash1

{
    "_id" : ObjectId("6039437ce0c7d52970d3f9f6"),
    "name" : "Prakash1",
    "latestMsg" : {
        "_id" : "6039437ce0c7d52970d3f9f36039437ce0c7d52970d3f9f6",
        "sender" : ObjectId("6039437ce0c7d52970d3f9f3"),
        "receiver" : ObjectId("6039437ce0c7d52970d3f9f6"),
        "message" : "Ok paste your query.",
        "time" : ISODate("2021-02-27T00:41:00.000+05:30")
    }
}

Test data:

Users

/* 1 createdAt:2/27/2021, 12:22:44 AM*/
{
    "_id" : ObjectId("6039437ce0c7d52970d3f9f6"),
    "name" : "Prakash1"
},

/* 2 createdAt:2/27/2021, 12:22:44 AM*/
{
    "_id" : ObjectId("6039437ce0c7d52970d3f9f5"),
    "name" : "Prakash2"
},

/* 3 createdAt:2/27/2021, 12:22:44 AM*/
{
    "_id" : ObjectId("6039437ce0c7d52970d3f9f3"),
    "name" : "Dheemanth"
}

Chats

/* 1 createdAt:2/27/2021, 1:21:05 PM*/
{
    "_id" : ObjectId("6039f9e9e0c7d52970d3fa43"),
    "sender" : ObjectId("6039437ce0c7d52970d3f9f6"),
    "receiver" : ObjectId("6039437ce0c7d52970d3f9f3"),
    "message" : "Hi im am Prakash1",
    "time" : ISODate("2021-02-27T00:39:00.000+05:30")
},

/* 2 createdAt:2/27/2021, 1:21:05 PM*/
{
    "_id" : ObjectId("6039f9e9e0c7d52970d3fa44"),
    "sender" : ObjectId("6039437ce0c7d52970d3f9f6"),
    "receiver" : ObjectId("6039437ce0c7d52970d3f9f3"),
    "message" : "Need some help!!!",
    "time" : ISODate("2021-02-27T00:40:00.000+05:30")
},

/* 3 createdAt:2/27/2021, 1:21:05 PM*/
{
    "_id" : ObjectId("6039f9e9e0c7d52970d3fa45"),
    "sender" : ObjectId("6039437ce0c7d52970d3f9f3"),
    "receiver" : ObjectId("6039437ce0c7d52970d3f9f6"),
    "message" : "Ok paste your query.",
    "time" : ISODate("2021-02-27T00:41:00.000+05:30")
},

/* 4 createdAt:2/27/2021, 1:21:05 PM*/
{
    "_id" : ObjectId("6039f9e9e0c7d52970d3fa46"),
    "sender" : ObjectId("6039437ce0c7d52970d3f9f5"),
    "receiver" : ObjectId("6039437ce0c7d52970d3f9f3"),
    "message" : "Hi im am Prakash2",
    "time" : ISODate("2021-02-27T00:37:00.000+05:30")
},

/* 5 createdAt:2/27/2021, 1:21:05 PM*/
{
    "_id" : ObjectId("6039f9e9e0c7d52970d3fa47"),
    "sender" : ObjectId("6039437ce0c7d52970d3f9f5"),
    "receiver" : ObjectId("6039437ce0c7d52970d3f9f3"),
    "message" : "Can u help me?",
    "time" : ISODate("2021-02-27T00:38:00.000+05:30")
}
user_50704600
Related