Sequelize many-to-many M:N relationship not functioning. Error: 'SequelizeEagerLoadingError:${model1} is not associated to ${model2}'

Question

I'm trying to make a many to many relationship between users and roles through a predefined table roleUsers.

const User = sequelize.define('User', {
    firstName: DataTypes.STRING,
    lastName: DataTypes.STRING,
    email: DataTypes.STRING,
    phone: DataTypes.STRING,
    password: DataTypes.STRING,
    deletedAt: DataTypes.DATE,
  },

  const Role = sequelize.define('Role', {
    roleName: DataTypes.STRING,
  }, 

  const RoleUser = sequelize.define('RoleUser', {
    userId: {
      type: DataTypes.INTEGER,
      references: {
        model: User,
        key: 'id',
      },
    },
    roleId: {
      type: DataTypes.INTEGER,
      references: {
        model: Role,
        key: 'id',
      },
    },
  },

  Role.belongsToMany(User, {
    through: RoleUser,
    foreignKey: 'roleId',
    otherKey: 'userId',
    as: 'users',
  });
  User.belongsToMany(Role, {
    through: RoleUser,
    foreignKey: 'userId',
    otherKey: 'roleId',
    as: 'roles',
  });

However when I try to call this relationship (or any other M:N relationship - 1:Ms and 1:1s are working fine) as below

    const roles = await Role.findAll({
      include: [
        {
          model: User,
          as: 'users',
          through: RoleUser,
        },
      ],
    });

I'm getting the error below

SequelizeEagerLoadingError: User is not associated to Role!

I've generally tried taking things out and putting back in in various combinations the defined foreign keys, other keys, aliases in the definition and varying how I call the models (removing aliases, through etc). Also I tried not specifying the referencing in the junction table.

My models and relationships are arranged on separate pages, but they're all joining up fine as I can see the 1:M relationships are working fine, it's only my M:M relationships that are messing up. I would be so grateful for any help!

Answer 1

You have used through table to associate users and roles so there will be no foreignKey column in table role and also in users table and if you want to add role_id in your user table than simply create column

    role_id: {
        type: DataTypes.INTEGER(),
        allowNull: true
    }

than add role_id with other data in user table set role like below user.setRoles(req.body.role_id) this will add data in UserRoles table

Roles.belongsToMany(Users, { through: 'UserRoles', foreignKey: 'role_id', otherKey: 'user_id' });
Users.belongsToMany(Roles, { through: "UserRoles", foreignKey: "user_id", otherKey: "role_id" });
UserRoles.belongsTo(Users, { foreignKey: 'user_id', targetKey: 'id' });
UserRoles.belongsTo(Roles, { foreignKey: 'role_id', targetKey: 'id' });

Than try api like below to fetch all user with their roles

                let role = await sequelize.UserRoles.findAll({
                include: [
                    {
                        model: sequelize.Users,
                        attributes: { exclude: ['password'] }
                    },
                    {
                        model: sequelize.Roles
                    }
                ]
            });

            let result = error.OK;
            result.data = role;

            logger.info(result);
            return res.status(200).send(result)

Above query will return data like below

"data": [
    {
        "user_id": 1,
        "role_id": 2,
        "createdAt": "2020-11-11T11:40:50.999Z",
        "updatedAt": "2020-11-11T11:40:50.999Z",
        "User": {
            "id": 1,
            "first_name": "admin1",
            "last_name": "Admin1",
            "user_name": "admin1",
            "email": "admin1@gmail.com"
            "role_id": 1,
            "user_type": "ADMIN",
            "createdAt": "2020-11-11T11:40:49.053Z",
            "updatedAt": "2020-11-11T11:40:49.053Z"
        },
        "Role": {
            "id": 1,
            "name": "ADMIN"
            "createdAt": "2020-11-11T11:37:47.638Z",
            "updatedAt": "2020-11-11T11:37:47.638Z"
        }
    }
],

Answer 2

If anyone else has this problem - the only way I could find to fix this was to state the relationship directly before using it rather than having it listed on a separate models/ relationship page.

Answer 3

I suppose indicating User with the alias users is enough for your request:

 const roles = await Role.findAll({
      include: [
        {
          model: User,
          as: 'users'
        },
      ],
    });