我在 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 直接在檔案集合中，但是對于另外兩個 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) 聯系時，我還有來自其他部門和小組的“星期五”檔案。如果您知道為什么會發生這種情況或其他解決方案，謝謝
uj5u.com熱心網友回復：
您可以將查找的檔案分為 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"
]
}
Here is the Mongo playground for your reference.
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/339373.html