node.js - 从每个用户的 userchat 获取最后一个文档 - mongoose

Question

用户架构

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 架构

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);

我需要一个查询来仅获取当前登录用户与所有其他用户的最后一次用户聊天，即当前登录用户可以是发送者或接收者的聊天，所有其他用户中的每一个也可以是发送者或接收者并且该聊天必须是登录用户和其他用户之间的最新聊天。

User and UserChat collections

例如如果当前登录的用户是 ObjectId("6039c2513b3fd51f3595d82f")，那么查询必须给出以下输出:

[
  {
    _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"),
    },
  },
]

Answer 1

答案场景:

1. 总共三个用户。
2. Prakash1、Prakash2 与 Dheemanth 交谈过两次。
3. Dheemanth 已回复 Prakash1，因此显示的最后一条消息是 Dheemanth 的回复。
4. Dheemanth 尚未回复 Prakash2，因此显示了 Prakash2 的最后一条消息。

查询:

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" }
]);

输出: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")
    }
}

输出: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")
    }
}

测试数据:

用户

/* 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"
}

聊天

/* 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")
}