用于向用户A返回最新消息以及从用户A返回最近消息的聚合查询工作不正常

Question

我有一个MostRecentMessage模型，定义为

const MostRecentMessage = new Schema({
to: {
type: mongoose.Schema.Types.ObjectId,
ref: "user"
},
from: {
type: mongoose.Schema.Types.ObjectId,
ref: "user"
},
conversation: {
type: mongoose.Schema.Types.ObjectId,
ref: "conversation"
},
date: {
type: Date,
default: Date.now
}
});

每当用户A向用户B发送消息时，如果文档不存在，就会创建该文档。每次用户A向用户B发送消息时，此文档都会更新为新的conversation和date。文件的一个例子是

_id: ObjectId(5dc46521cf670a0017d2434d)
to: ObjectId(5dc464ce2fd75700178c1ad4) // User B
from: ObjectId(5dc464fc2fd75700178c1ad5) // User A
conversation: ObjectId(5dc465c6cf670a0017d24363)
date: 2019-11-07T18:40:33.242+00:00
__v: 0

其想法是，这将有助于跟踪所有最近发给特定用户的消息。

同样，用户B可以向用户A发送消息。将创建一个新的文档，类似于上面的

_id: ObjectId(5dc46521cf670a0017d2434d)
to: ObjectId(5dc464fc2fd75700178c1ad5) // User A
from: ObjectId(5dc464ce2fd75700178c1ad4) // User B
conversation: ObjectId(5dc465c6cf670a0017d24363)
date: 2019-11-07T18:40:33.242+00:00
__v: 0

用户B向用户A发送的每一条新消息都会更新此文档。

现在，下面是我的聚合查询。。但问题是它只返回一个对话。可能有10个用户与用户A对话，但它仍然只返回一个文档。

const { id } = req.user;
try {
await MostRecentMessages.aggregate(
[
{
$match: {
$or: [
{ from: mongoose.Types.ObjectId(id) },
{ to: mongoose.Types.ObjectId(id) }
]
}
},
{ $project: { _id: 1, from: 1, to: 1, conversation: 1, date: 1 } },
{ $sort: { date: -1 } },
{
$group: {
_id: null,
from: { $first: "$from" },
to: { $first: "$to" },
date: { $first: "$date" },
conversation: { $first: "$conversation" }
}
},
{
$lookup: {
from: "conversations",
localField: "conversation",
foreignField: "_id",
as: "conversation"
}
},
{ $unwind: { path: "$conversation" } },
{
$lookup: {
from: "users",
localField: "to",
foreignField: "_id",
as: "to"
}
},
{ $unwind: { path: "$to" } },
{
$lookup: {
from: "users",
localField: "from",
foreignField: "_id",
as: "from"
}
},
{ $unwind: { path: "$from" } }
],
function(err, docs) {
if (err) {
console.log(err);
} else {
return res.json(docs);
}
}
);
} catch (err) {
console.log(err);
return res.status(500).send("Server error");
}

我在这里做错了什么？

---

编辑：我现在拥有的：

有了这个代码，如果

User A messages User B, 'Test1'
User B messages User A, 'Test2'
User A messages User B, 'Test3'

则对于两个用户将仅显示CCD_ 4消息。如何为两个用户显示"Test3"？

await MostRecentMessages.aggregate(
[
{
$match: {
$or: [
{ from: mongoose.Types.ObjectId(id) },
{ to: mongoose.Types.ObjectId(id) }
]
// deletedBy: { $ne: id }
}
},
{ $project: { _id: 1, from: 1, to: 1, conversation: 1, date: 1 } },
{ $sort: { date: -1 } },
{
$group: {
_id: {
userConcerned: {
$cond: {
if: {
$eq: ["$to", mongoose.Types.ObjectId(id)]
},
then: "$to",
else: "$from"
}
},
interlocutor: {
$cond: {
if: {
$eq: ["$to", mongoose.Types.ObjectId(id)]
},
then: "$from",
else: "$to"
}
}
},
from: { $first: "$from" },
to: { $first: "$to" },
date: { $first: "$date" },
conversation: { $first: "$conversation" }
}
},
{
$lookup: {
from: "conversations",
localField: "conversation",
foreignField: "_id",
as: "conversation"
}
},
{ $unwind: { path: "$conversation" } },
{
$lookup: {
from: "users",
localField: "to",
foreignField: "_id",
as: "to"
}
},
{ $unwind: { path: "$to" } },
{
$lookup: {
from: "users",
localField: "from",
foreignField: "_id",
as: "from"
}
},
{ $unwind: { path: "$from" } }
],
function(err, docs) {
if (err) {
console.log(err);
} else {
return res.json(docs);
}
}

Answer 1

正如我在评论中所说，小组阶段对您获得的唯一结果负责。诀窍是创建两个字段，从"from"或"to"字段(取决于您在param中提供的objectId(，代表您提供的用户和他的对话者。(无论"从"还是"到"(这是一个想法，但我目前无法测试解决方案。

...
{
$group: {
_id: {
userConcerned: {
$cond: {
if: {
$eq: [
"$to",
mongoose.Types.ObjectId(id)
]
},
then: "$to",
else: "$from"
}
},
interlocutor: {
$cond: {
if: {
$eq: [
"$to",
mongoose.Types.ObjectId(id)
]
},
then: "$from",
else: "$to"
}
}
},
from: { $first: "$from" },
to: { $first: "$to" },
date: { $first: "$date" },
conversation: { $first: "$conversation" }
}
}
...

这是POC

(如果你真的不在乎"from"one_answers"to"，你可以使用两个新创建的字段进行查找，并从组阶段中删除from和to

希望它能帮助

---编辑---完成并解释我的答案：使用此计算的_id而不是null进行分组，将对A和B(A->B或B->A(、A和C、A和D之间的任何会话进行分组，。。。一个总是在"userConcerned"中，另一个是对话者。通过这种方式，你将为a的每次对话获得一份文档，其中包含a和对话者之间的最新消息(由于之前的$sort阶段和$first累加器(，而不必考虑a是消息的发送者还是接收者(发件人或收件人(。