mongodb - 带有查找的外部集合的MongoDB条件
问题描述
我在 MongoDB 4.0 (pymongo) 中有三个集合
users: [
{_id: "aaa", name: "John", user_type: "writer", department: {value: "1", label:"press"}},
{_id: "bbb", name: "Charles", user_type: "writer", department: {value: "1", label:"press"}},
{_id: "ccc", name: "Jessy", user_type: "admin_writer", department: {value: "1", label:"press"}},
{_id: "ddd", name: "Tim", user_type: "writer", department: {value: "2", label:"news"}},
{_id: "eee", name: "Max", user_type: "admin_writer", department: {value: "2", label:"news"}},
{_id: "fff", name: "Julia", user_type: "admin", department: {value: "2", label:"news"}},
{_id: "ggg", name: "Arnold", user_type: "writer", department: {value: "3", label:"infos"}}
]
departments: [
{_id: 1, name: "press", group: "times"},
{_id: 2, name: "news", group: "times"},
{_id: 3, name: "infos", group: "herald"}
]
docs: [
{_id: 1, name: "monday", user_id: "aaa"},
{_id: 2, name: "tuesday", user_id: "bbb"},
{_id: 3, name: "wednesday", user_id: "ddd"},
{_id: 4, name: "thursday", user_id: "ddd"},
{_id: 5, name: "friday", user_id: "ggg"}
]
在我的示例中,用户可以编写文档并在部门工作。部门由组(社会)定义。作家只能看到他的文档,admin_writer 可以看到他部门的所有文档,包括他编写的文档,管理员可以看到组中的所有文档,即使他在特定部门工作。
当用户登录应用程序时,我需要在仪表板中列出所有文档
例如,结果将是:
John (writer) : [{name: "monday"}]
Jessy (admin_writer) : [{name: "monday"}, {name: "tuesday"}]
Max (admin_writer) : [{name: "wednesday"}, {name: "thursday"}]
Julia (admin) : [{name: "monday"}, {name: "tuesday"},{name: "wednesday"}, {name: "thursday"}]
Arnold (writer) : [{name: "friday"}]
为此,我管理每个特定 user_type 的查询。对于作家,我使用$match因为 user_id 直接在 docs 集合中,但对于其他两个 user_type,我需要使用 $lookup 在外部集合中添加条件。我尝试了两种方法(我使用 Pymongo):
user_department = 1
db.docs.aggregate([{
'$lookup': {
'from': "users",
'localField': "user_id",
'foreignField': "_id",
'as': "user"
}
},{
"$addFields": {
"user": {
"$arrayElemAt": [
{
"$filter": {
"input": "$user",
"as": "userId",
"cond": {
"$eq": ["$$userId.department.value", user_department]
}
}
}, 0
]
}
}
}, {'$project: {'name': 1}]);
或者
db.docs.aggregate([{
"$lookup": {
"from": "users",
"let": {
'id': "$user_id"
},
"pipeline": [
{
'$match': {
'$expr': {
'$eq': [
"$_id",
"$$id"
]},
"_id": user_department
}
}
],
"as": "user"
}
}]);
在我的两次尝试中,我没有预期的结果。当我与 admin_writer (Jessy) 联系时,我也有来自其他部门和小组的“星期五”文档。如果您知道为什么会发生这种情况或其他解决方案,谢谢
解决方案
您可以将查找的文档分为 3 种情况:
- 用户可以自己查看的文档
- 如果他/她是 admin_writer,用户可以按部门查看的文档
- 如果他/她是管理员,用户可以查看的文档(即所有文档) 可查看文档的最终列表将是上述 3 种情况的联合。
用户可以自己查看的文档
一个微不足道的$lookup
就足够了
{
"$lookup": {
"from": "docs",
"localField": "_id",
"foreignField": "user_id",
"as": "selfDocs"
}
}
如果他/她是 admin_writer,用户可以按部门查看的文档
您首先需要获取同一部门的完整用户列表。$and
在子管道中添加条件和查找以检查所有部门用户的 user_type 和文档
{
"$lookup": {
"from": "users",
"localField": "department.value",
"foreignField": "department.value",
"as": "deptUsers"
}
},
{
"$lookup": {
"from": "docs",
"let": {
userType: "$user_type",
deptUsers: "$deptUsers"
},
pipeline: [
{
$match: {
$expr: {
$and: [
{
$eq: [
"$$userType",
"admin_writer"
]
},
{
$in: [
"$user_id",
"$$deptUsers._id"
]
}
]
}
}
}
],
"as": "deptDocs"
}
}
如果他/她是管理员,用户可以查看的文档(即所有文档)
$lookup
部门找出用户的组。然后使用用户的组来查找组内的所有用户。进行条件检查,user_type: "admin"
文档属于同一组中的用户。
{
"$lookup": {
"from": "departments",
let: {
dept: "$department.value"
},
pipeline: [
{
$match: {
$expr: {
$eq: [
"$$dept",
{
$toString: "$_id"
}
]
}
}
}
],
"as": "group"
}
},
{
"$unwind": "$group"
},
{
"$set": {
"group": "$group.group"
}
},
{
"$lookup": {
"from": "users",
"let": {
group: "$group"
},
"pipeline": [
{
"$lookup": {
"from": "departments",
let: {
d: "$department.value"
},
pipeline: [
{
$match: {
$expr: {
$eq: [
"$$d",
{
$toString: "$_id"
}
]
}
}
}
],
"as": "gp"
}
},
{
"$unwind": "$gp"
},
{
$match: {
$expr: {
$eq: [
"$gp.group",
"$$group"
]
}
}
},
{
"$project": {
"_id": 1
}
}
],
"as": "groupUsers"
}
},
{
"$lookup": {
"from": "docs",
"let": {
userType: "$user_type",
groupUsers: "$groupUsers"
},
pipeline: [
{
$match: {
$expr: {
$and: [
{
$eq: [
"$$userType",
"admin"
]
},
{
$in: [
"$user_id",
"$$groupUsers._id"
]
}
]
}
}
}
],
"as": "adminDocs"
}
}
用于$setUnion
将它们链接起来
allDocs: {
"$setUnion": [
"$selfDocs",
"$deptDocs",
"$adminDocs"
]
}
这是Mongo游乐场供您参考。
推荐阅读
- resources - 厨师执行资源不适用于 Rhel 8
- macos - LaunchD Rclone 挂载脚本未加载
- python - 为什么在 Linux (NAS) 上安装 pandas 时出现错误?
- javascript - 我可以使获取请求看起来像浏览器吗?
- google-analytics - 带条件的 Google 数据洞察总和
- javascript - 为什么在我输入文本框时字符会从文本框中删除?
- java - java中cmd.exe中Unicode字符的部分显示,shell命令中没有字符
- javafx - javaFX:MediaPlayer 的 seek 方法挂起播放器而没有错误消息或状态更改
- c++ - 递归回溯以查找所有子集
- python - 这个 Big-O 表示法对于这个简单排序函数 (Python) 是否正确?