I have 3 tables: Users, Departments and Roles. I would like to get user roles for specified department. User can have different role for each department. I can't find a solution to create links between tables. For an idea in json format, I would do it like this:

{
    users: [
        {
            id: 1,
            email: "[email protected]",
            password: "password",
            name: "Mark",
            lastname: "Twain",
            departments: [
                {
                    id: 1,
                    name: "Department A",
                    role: "admin"
                },
                {
                    id: 2,
                    name: "Department B",
                    role: "user"
                }
            ] 
        },
        {
            id: 2,
            email: "[email protected]",
            password: "password",
            name: "Joe",
            lastname: "Ritter",
            departments: [
                {
                    id: 1,
                    name: "Department A",
                    role: "superadmin"
                },
                {
                    id: 2,
                    name: "Department B",
                    role: "user"
                },
                {
                    id: 3,
                    name: "Department C",
                    role: "user"
                }
            ] 
        }
    ]
}

Does anybody know how to turn this into MySQL? Thank you

CodePudding user response：

If a user can have only one role for a certain department (as you mentioned in the comments) you need a junction table UserDepartment that should have links to User, Deparment and Role models and associations should look like this:

// M:N relationship between users and deparments
User.belongsToMany(Department, { through: UserDeparment });
// N:1 relationship that indicates a role for a certain pair of user-department
UserDepartment.belongsTo(Role);

A query to get a role for a certain user and a department might look like this:

const userDepartment = UserDeparment.findOne({
  where: {
    userId,
    departmentId
  },
  attributes: [],
  include: [{
    model: Role
  }]
})

This will get you an object like this:

{
  role: {
    id: 1,
    name: 'user'
  }
}