I have three collections in 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"}
]

In my example, user can write a doc and work in a department. A department is defined by is group (society). A writer can see only his docs, an admin_writer can see all docs from his department include a doc he writes and admin can see all docs in the group evenif he works on a specific department.

I need to have the list of all docs in dashboard when a user logs in the application

For example, the result will be :

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"}]

For that, I manage queries for each specific user_type. For a writer, I use a $match because user_id is directly in docs collections, but for the two others user_type, I need to use $lookup to add condition in foreign collection. I tried two ways (I use 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}]);

or

db.docs.aggregate([{
                "$lookup": {
                    "from": "users",
                    "let": {
                        'id': "$user_id"
                    },
                    "pipeline": [
                        {
                            '$match': {
                                '$expr': {
                                    '$eq': [
                                        "$_id",
                                        "$$id"
                                    ]},
                                "_id": user_department
                            }
                        }
                    ],
                    "as": "user"
                }
            }]);

In my two tries, I don't have expected result. When I connect with an admin_writer (Jessy), I also have the "friday" doc from an other department and group. If you have an idea on why it's happen or another solution, Thanks

CodePudding user response：

You can divide your looked up docs into 3 cases:

1. the docs that the user can view by himself/herself
2. the docs that the user can view by department if he/she is admin_writer
3. the docs that the user can view if he/she is admin(i.e. all the docs) The final list of viewable docs will be a union of the above 3 cases.

the docs that the user can view by himself/herself

A trivial $lookup will suffice

{
    "$lookup": {
      "from": "docs",
      "localField": "_id",
      "foreignField": "user_id",
      "as": "selfDocs"
    }
}

the docs that the user can view by department if he/she is admin_writer

You will need first to get a full list of users in same department first. Add a $and condition and the lookup in sub-pipeline to check for the user_type and the docs by all the department users

{
    "$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"
    }
}

the docs that the user can view if he/she is admin(i.e. all the docs)

$lookup departments to find out the group of the user. Then used the user's group to lookup all the users within the group. Do a conditional check for user_type: "admin" and the doc belongs to user in the same group.

{
    "$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"
    }
  }

Use $setUnion to chain them up

allDocs: {
  "$setUnion": [
    "$selfDocs",
    "$deptDocs",
    "$adminDocs"
  ]
}

Here is the Mongo playground for your reference.