如何获取用户对话的最后一条消息并将其分组到mongodb的列表中

Question

我想获得一个用户和另一个用户之间的最后一条消息，该消息位于一个名为chats的集合中，所有成员之间都有对话，但是我想进行过滤，以便只显示一个用户和另一个用户之间的最后一条消息。有什么办法在mongodb中得到这个吗?

例如，我想获得用户Carlos发送/接收的最后消息。我想要一个这样的列表

例子:

Chat between John Doe and me
John Doe: Hi
Chat between Max and me
Max: Hello
Chat between Jessica and me
Owner: Hello Jessica

模型

1. owner = ObjectId
2. recipient = ObjectId
3. content = Message content
4. createdAt / updatedAt = Autogenerate

我正在尝试这个，但是错误是我得到了用户收到的最后一条消息以及用户发送给该用户的最后一条消息

代码I am trying

const chatData = await this.chatModel
.find({ $or: [{ owner: userId }, { recipient: userId }] })
.populate('owner', DEFAULT_POPULATE_SELECT_USER)
.populate('recipient', DEFAULT_POPULATE_SELECT_USER)
.sort({ createdAt: 'desc' })
.select({ __v: false })
.lean();
const chatLog: ChatModel[] = chatData.reduce((unique, item: any) => {
if (!item.recipient) return unique;
return unique.some((x) => x.recipient._id == item.recipient._id) ? unique : [...unique, item];
}, []);

Answer 1

试试这个查询

用户模式示例

{
_id: ObjectId,
name: String,
createdAt: Date,
updatedAt: Date
}

聊天模式示例

{
owner: ObjectId,
recipient: ObjectId,
content: String
createdAt: Date,
updatedAt: Date
}

查询

const userId = ObjectId('xxxx...')
await chatModel.aggregate([
{ $match: { $or: [ { owner: userId }, { recipient: userId }] } },
{ $addFields: {
me: { $cond: [ { $ne: [ '$owner', userId ] }, '$recipient', '$owner' ] },
other: { $cond: [ { $ne: [ '$owner', userId ] }, '$owner', '$recipient' ] },
} },
{ $group: { _id: { me: '$me', other: '$other' }, document: { $last: '$$ROOT' } } },
{ $replaceRoot: { newRoot: '$document' } },
{ $lookup: { from: 'users', let: { owner: '$owner' }, as: 'ownerInfo', pipeline: [ { $match: { $expr: { $and: [ { $eq: [ '$_id', '$$owner' ] } ] } } } ] } },
{ $unwind: '$ownerInfo' },
{ $lookup: { from: 'users', let: { recipient: '$recipient' }, as: 'recipientInfo', pipeline: [ { $match: { $expr: { $and: [ { $eq: [ '$_id', '$$recipient' ] } ] } } } ] } },
{ $unwind: '$recipientInfo' },
{ $sort: { createdAt: -1 } }
])