我正在try 查找所有未读消息，通过聊天ID和用户ID.未读消息是如果chats_users.max_read_date小于message.create_date,message.from_id不等于用户.
它现在是如何运作的.我有一个聊天ID数组，我按chat_id和user_id搜索所有的Chats_USERS文档，然后循环搜索消息.
我想将其全部重写为一个查询，但我遇到了一些问题.我的问题是什么？我如何通过聊天ID和用户ID找到全部chats_users条消息，然后找到非用户写的所有消息，并且创建日期message.create_date大于chats_users.max_read_date
如何创建一个条件，以便对于每个聊天，找到它的Chats_USERS，然后只 Select 那些不是由发出请求的用户和所有其他聊天者写的消息，并且消息message.create_date的创建日期大于chats_users.max_read_date
消息收集
type ChatsUsers struct {
ID string `json:"id" bson:"id"`
ChatID string `json:"chat_id" bson:"chat_id"`
// user ID, which corresponds to the user ID in the system.
UserID string `json:"user_id" bson:"user_id"`
// MaxReadDate time of the last message read
MaxReadDate int64 `json:"max_read_date" bson:"max_read_date"`
}
ChatsUser集合
type Message struct {
ID string `json:"id" bson:"id"`
ChatID string `json:"chat_id" bson:"chat_id"`
FromID string `json:"from_id" bson:"from_id"`
CreateDate int64 `json:"create_date" bson:"create_date"`
Body string `json:"body" bson:"body"`
}
Rewrite the query to get a single aggregation, the purpose of the query is to find all messages, by all chats that are unread and unscripted by a non-user, the uid variable is the user looking for such messages
fun (r *Mongo) UnreadMessageCount(ctx context.Context, chats []*Chat, uid string) (map[string]int64, error) {
match := bson.A{}
for _, chat := range chats {
match = append(match, chat.ID)
}
chatsUsersList := make([]*domain.ChatsUsers, 0)
for _, ch := range chats {
chu, err := r.FindChatUser(ctx, ch.ID, uid)
if err != nil {
l.Error().Err(err).Msg("failed to find chat user")
return nil, err
}
chatsUsersList = append(chatsUsersList, chu)
}
list := make([]*domain.Message, 0)
for _, ch := range chats {
for _, chu := range chatsUsersList {
if chu.ChatID == ch.ID {
filter := bson.D{
// search for messages by active chat IDs
{ Key: "chat_id", Value: ch.ID},
// add filtering: that the message has not yet been read,
// and that the messages we select are not written by the current user.
{Key: "$and",
Value: bson.A{
bson.D{
{
Key: "create_date", Value: bson.D{ bson.D{
// $gt Matches values that are greater than the specified value.
{ Key: "$gt", Value: chu.MaxReadDate}}
},
// $ne Matches all values that are not equal to the specified value.
// https://www.mongodb.com/docs/manual/reference/operator/query-comparison/
{Key: "from_id", Value: bson.M{"$ne": uid}}
},
},
},
}
cursor, err := r.colMessage.Find(ctx, filter)
....
var res []*domain.Message
....
}
}
}
// it doesn't make sense to use an array of messages, we need to create a map,
// which will have the chat ID and the number of unread messages in it.
messages := make(map[string]int64)
for _, msg := range list {
messages[msg.ChatID]++
}
return messages, nil
}
我的try (抱歉，这是一个非常不正确的查询，但我仍在try 编写正确的查询)
db.message.aggregate([
{
$match: {
$expr: {
$and: [
{
chat_id: {
$in: ["ad0a3405-1a16-48f9-93e6-51b17a7283e2"]
}
},
{
from_id: {
$ne: "63f5002735bb916dab3f2b1d"
}
},
{
$gt: ["$create_date", { $max: "$chats_users.max_read_date" }]
}
]
}
}
},
{
$lookup: {
from: "chatsusers",
localField: "chat_id",
foreignField: "chat_id",
as: "chats_users"
}
},
{
$unwind: "$chats_users"
},
{
$group: {
_id: "$chat_id",
messages: {
$push: {
id: "$id",
chat_id: "$chat_id",
from_id: "$from_id",
create_date: "$create_date",
type: "$type",
media: "$media",
body: "$body",
update_at: "$update_at",
modifications: "$modifications",
viewed: "$viewed"
}
},
max_read_date: { $max: "$chats_users.max_read_date" }
}
}
]);
在响应中，我只希望从测试数据中获得四个对象
{
"_id": {
"$oid": "63f502d3c1be2b78aaa6152e"
},
"body": "полковник конь тобі копитом в грудь! 3",
"chat_id": "ad0a3405-1a16-48f9-93e6-51b17a7283e2",
"create_date": 1677001427977,
"from_id": "63f5002735bb916dab3f2b1d5g",
"id": "G30uZ1HvlYQCZVwRRTBi",
"media": "",
"modifications": null,
"type": "text",
"update_at": 0,
"viewed": false
},
{
"_id": {
"$oid": "63f502d2c1be2b78aaa6152d"
},
"body": "полковник конь тобі копитом в грудь! 2",
"chat_id": "ad0a3405-1a16-48f9-93e6-51b17a7283e2",
"create_date": 1677001428101,
"from_id": "63f5002735bb916dab3f2b1d5g",
"id": "RzrfvZuLWyhrKOHEx5gK",
"media": "",
"modifications": null,
"type": "text",
"update_at": 0,
"viewed": false
},
{
"_id": {"$oid": "63f5002735bb916dab3f2bb2"},
"body": "after you",
"chat_id": "ad0a3405-1a16-48f9-93e6-51b17a7283e2-y7y3r2",
"create_date": 1677001427398,
"from_id": "63f5002735bb916dab3f2b23",
"id": "e5189d64-0c43-41dd-befa-f554d09475cf",
"media": "",
"modifications": null,
"type": "text",
"update_at": 0,
"viewed": true
},
{
"_id": {"$oid": "63f5002735bb916dab3f2bb1"},
"body": "I'm not religious",
"chat_id": "ad0a3405-1a16-48f9-93e6-51b17a7283e2-y7y3r2",
"create_date": 1677001427327,
"from_id": "63f5002735bb916dab3f2b23",
"id": "a1fbc6b0-5f8d-4fa2-aef7-807314dfba0d",
"media": "",
"modifications": null,
"type": "text",
"update_at": 0,
"viewed": true
}
我的第二次try
db.chats_users.aggregate([
{
$match: {
user_id: "63f5002735bb916dab3f2b1d",
chat_id: { $in: ["ad0a3405-1a16-48f9-93e6-51b17a7283e2"] },
}
},
{
$lookup: {
from: "message",
let: { chat_id: "$chat_id", max_read_date: "$max_read_date" },
pipeline: [
{
$match: {
$expr: {
$and: [
{ $eq: ["$chat_id", "$$chat_id"] },
{ $ne: ["$from_id", "63f5002735bb916dab3f2b1d"] },
{ $gt: ["$create_date", "$$max_read_date"] },
]
}
}
}
],
as: "messages"
}
},
{
$project: {
chat_id: 1,
message_count: { $size: "$messages" }
}
}
])