5

I have a situation where I have a type of group document. I want to have a list field that contains a reference Id to the users in the group. I need to however indicate which users have admin access. Should I have two lists, one of regular users and one of admins, or have a custom document that I embed a list of that just has the reference Id and a bool value? This is basically a many to many, with both documents have a list of reference Ids to the other documents. I'm just not sure how to include this other value.

If it makes any difference I'm using Python/Mongoengine to access the MongoDB

Jhorra
  • 6,233
  • 21
  • 69
  • 123

1 Answers1

3

There are various ways of modelling your requirement in the current form. I'll try to show you one such way and uses $lookup. You should try with two separate collections one for each groups and users like below.

One other option will be to use $DBRef which will eagerly load all the users in the group when you fetch group collection. This option will depend on python driver and I'm sure driver supports that.

Groups Document

{
    "_id": ObjectId("5857e7d5aceaaa5d2254aea2"),
    "name": "newGroup",
    "usersId": ["user1", "user2"]
}

Users Document

{ "_id" : "user1", "isAdmin" : true }
{ "_id" : "user2" }

Get All Users in a group

db.groups.aggregate({
    $unwind: '$usersId'
}, {
    $lookup: {
        from: "users",
        localField: "usersId",
        foreignField: "_id",
        as: "group_users"
    }
})

Response

{
    "_id": ObjectId("5857e7d5aceaaa5d2254aea2"),
    "name": "newGroup",
    "usersId": "user1",
    "group_users": [{
        "_id": "user1",
        "isAdmin": true
    }]
} {
    "_id": ObjectId("5857e7d5aceaaa5d2254aea2"),
    "name": "newGroup",
    "usersId": "user2",
    "group_users": [{
        "_id": "user2"
    }]
}

Get All Admin Users in a group

db.groups.aggregate({
    $unwind: '$usersId'
}, {
    $lookup: {
        from: "users",
        localField: "usersId",
        foreignField: "_id",
        as: "group_users"
    }
}, {
    $match: {
        "group_users.isAdmin": {
            $exists: true
        }
    }
})

Response

{
    "_id": ObjectId("5857e7d5aceaaa5d2254aea2"),
    "name": "newGroup",
    "usersId": "user1",
    "group_users": [{
        "_id": "user1",
        "isAdmin": true
    }]
}

Based on comment:

The is admin is admin for the group, so I can't store it in the users table. This is a many to many relationship.

I think you should include both the regular users list and admin users list. This will use the indexes you add and will make your read queries really straight forward.

Groups Document

{ "_id" : "newGroup", "userIds" : [ "user1" ], "adminIds" : [ "user2" ] }

Users Document

{ "_id" : "user1", "groupIds" : [ "newGroup" ] } -- regular user in newGroup
{ "_id" : "user2", "groupIds" : [ "newGroup" ] } -- admin user in newGroup.
{ "_id" : "user3", "groupIds" : [ ] }

Get All Regular Users

db.groups.aggregate({
    $unwind: '$userIds'
}, {
    $lookup: {
        from: "users",
        localField: "userIds",
        foreignField: "_id",
        as: "group_users"
    }
})

Get All Admin Users

db.groups.aggregate({
    $unwind: '$adminIds'
}, {
    $lookup: {
        from: "users",
        localField: "adminIds",
        foreignField: "_id",
        as: "group_users"
    }
})
s7vr
  • 73,656
  • 11
  • 106
  • 127
  • The is admin is admin for the group, so I can't store it in the users table. This is a many to many relationship. – Jhorra Dec 19 '16 at 15:25
  • Couple of questions here. Many to Many meaning the user knows which group he is part of meaning you will have a list of groups in each user document ? Other question How do you pick a user as a admin ? Is that something you assign some users as admin for each group when you create a group ? Updated answer based on this comment. – s7vr Dec 19 '16 at 17:36
  • So this app has various locations. Users have access to the locations they are attached to. Admins at each locations have permissions to edit that location. A user can be attached to many groups, and a group has many users. Some users of each group will be admins. So in other instances of many to many I have seen them use a list of users in the group and a list of groups for each user. I need to do that, but also indicate if you're an admin as well. – Jhorra Dec 19 '16 at 18:09
  • If this were a sql db I would have my join table with the user id and location id, but also a boolean value to indicate admin. – Jhorra Dec 19 '16 at 18:09
  • Did you get a chance to look at the updated answer and let me know your feedback. – s7vr Dec 19 '16 at 18:27
  • So basically have two lists, one for regular groups and one where you're an admin. – Jhorra Dec 19 '16 at 19:53