Trying to search entity with association Many to Many Sequelize

Viewed 127

PROBLEM RESUME: I'm having trouble when I try to do a findOne or findAll. At the findOne or findAll answer I catch all the informations from the user but in the answer there aren't any data of "t_roles" associated to this user. But the stranger issue is that if I use raw: true inside the findOne for example, the informations of roles are shown.

I Have two models

User:

const dbUser = {
  a_id: {
    primaryKey: true,
    autoIncrement: true,
    type: Sequelize.BIGINT,
  },
  a_date_created: {
    type: Sequelize.DATE,
    allowNull: false,
  },
  a_first_name: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  a_last_name: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  a_email: {
    type: Sequelize.TEXT,
    unique: true,
    allowNull: false,
  },
  a_password: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  a_birthday: {
    type: Sequelize.DATE,
    allowNull: false,
  },
  a_is_active: {
    type: Sequelize.BOOLEAN,
    allowNull: false,
  },
};

User.init(dbUser, {
  sequelize: db,
  modelName: 't_user',
  timestamps: false,
  tableName: 't_users',
});


User.associate = (models) => {
  console.log('ASSOCIADO')
  User.belongsToMany(models.Role, {
    through: { model: UserRole, unique: false },
    as: 'roles',
    foreignKey: 'a_user',
    otherKey: 'a_role',
  });
};

and Role:

const dbRole = {
  a_id: {
    primaryKey: true,
    autoIncrement: true,
    type: Sequelize.BIGINT,
  },
  a_role: {
    type: Sequelize.STRING,
    allowNull: false,
  },
};

Role.init(dbRole, {
  sequelize: db,
  modelName: 't_role',
  timestamps: false,
  tableName: 't_roles',
});

Role.associate = (models) => {
  Role.belongsToMany(models.User, {
    through: { model: UserRole, unique: false },
    as: 'UserOfRoles',
    foreignKey: 'a_role',
    otherKey: 'a_user',
  });
};

As you can see I'm associating them using another model, UserRole:

const dbUserRole = {
  a_id: {
    primaryKey: true,
    autoIncrement: true,
    type: Sequelize.BIGINT,
  },
  a_role: {
    type: Sequelize.INTEGER,
    allowNull: false,
    primaryKey: false,
    references: {
      model: Role,
      key: 'a_id',
    },
    onDelete: 'CASCADE',
  },
  a_user: {
    type: Sequelize.INTEGER,
    allowNull: false,
    references: {
      model: User,
      key: 'a_id',
    },
    onDelete: 'CASCADE',
  },
};

UserRole.init(dbUserRole, {
  sequelize: db,
  modelName: 't_user_role',
  timestamps: false,
  tableName: 't_user_role',
});


UserRole.associate = (models) => {
  UserRole.belongsTo(models.User, { targetKey: 'a_id', foreignKey: 'a_user' });
  UserRole.belongsTo(models.Role, { targetKey: 'a_id', foreignKey: 'a_role' });
};

To create a user with a role (admin) I do like the code below:

onst createAdmin = async (body) => {
  try {
    const userResult = await createUser(body);
    if (userResult.error) {
      return {
        ok: false,
        error: userResult.error,
      };
    }
    const isAdmin = await UserRole.create({
      a_role: 1,
      a_user: userResult.a_user_id,
    });

    return {
      ok: true,
    };
  } catch (error) {
    return {
      ok: false,
      error,
    };
  } 

Seems to be working fine, because the user are being created, and the association using the "t_user_role" too, because the data is also being created at the table. As I sad at the problem resume, my trouble is when I'm trying to do a findOne or findAll. For example, when I try the code below, I catch all the informations from the user but in the answer there aren't any data of "t_roles" associated to this user.

const { body } = req;
  try {
    const result = await User.findOne({
      where: {
        a_id: 1,
      },
      include: [
        {
          association: 'roles',
          attributes: ['a_role'],
          through: {
            attributes: [],
          },
        },
      ],
    });
    console.log('====================================');
    console.log(JSON.stringify(result, null, 2));
    console.log('====================================');
  } catch (error) {
    console.error(error);
  } 

If I use raw: true inside the findOne for example, the informations of roles are shown, so I presume that the association is correct. I really appreciate any help to find what I'm missing here. Thanks

1 Answers

Well, after days working on and trying different ways to solve this problem, a friend of mine just helped me starting again the entire project, following the documentation of Sequelize and the exact structure we did before, but a little bit more simple, and surprisingly ... worked. So I suppose that was something with migrations ore models or whatever, but we can't really say.

Related