How to generate query on join table using Sequelize?

2
votes

Let us say there are two tables namely User and User Role. The relationship between user and user role is one to many. Sequelize model for the user is as following -

const user = sequelize.define(
  'user', {
    id: {
      type: DataTypes.BIGINT,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
      field: 'id'
    },
    userName: {
      type: DataTypes.STRING(200),
      allowNull: false,
      field: 'username'
    },
    password: {
      type: DataTypes.STRING(200),
      allowNull: false,
      field: 'password'
    }
  }, {
    tableName: 'user'
  }
);

Sequelize model for user role is as follwing -

const userRole = sequelize.define(
  'userRole', {
    id: {
      type: DataTypes.BIGINT,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
      field: 'id'
    },
    userId: {
      type: DataTypes.BIGINT,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
      field: 'user_id'
    },
    password: {
      type: DataTypes.STRING(200),
      allowNull: false,
      field: 'password'
    }
  }, {
    tableName: 'userRole'
  }
);

Sequelize association is defined as follows -

user.hasMany(models.userRole, { foreignKey: 'user_id', as: 'roles' });
userRole.belongsTo(models.user, { foreignKey: 'user_id', as: 'user' });

I want to generate the following query using 

Sequelize -
SELECT * 
FROM   USER 
       INNER JOIN (SELECT user_role.user_id, 
                          role 
                   FROM   user_role 
                          INNER JOIN USER tu 
                                  ON tu.id = user_role.user_id 
                   GROUP  BY user_id 
                   ORDER  BY role) AS roles 
               ON USER.id = roles.user_id;

I am developing an API which will be consumed by the front end grid for showing user info. There is search functionality on role attribute of user role table. If any of role of a specific user is matched then I expect a user record with all the roles which are associated with that user.

2
sqlnode.jssequelize.js

2 Answers

1
votes

To get all roles that are associated with the user even if one of them matches with a query you need to define another association so that you can include role table twice in sequelize statement. 

User.hasMany(models.UserRole, { foreignKey: 'user_id', as: 'roles2' });

Sequelize statement - 

const userInfo = await User.findAndCountAll({
  include: [
    {
      model: UserRole,
      attributes: ['id', 'role'],
      as: 'roles',
      where: { [Op.or]: [{ role: { [Op.like]: '%MANAGER%' } },
      required: true
    },
    {
      model: UserRole,
      attributes: ['id', 'role'],
      as: 'roles2',
      required: true
    } 
  ],
  where: whereStatement,
});

With the help of first include (join), you can filter user records based on user role and with the help of the second include(join), you can get all the roles of a user whose one of the roles is matched.

0
votes

You have to use include (regarding the doc : https://sequelize.org/master/manual/models-usage.html#-code-findandcountall--code----search-for-multiple-elements-in-the-database--returns-both-data-and-total-count)

Exemple :


 Models.User.findAll({
    where :{
      id: userId
    },
    group: ['roles.user_id'],
    order: [['roles.role', 'ASC']] //or DESC, as you want
    include: {
      model: Models.UserRole,
      as: 'roles',
      attributes: ['user_id', 'role'],
      required: true
    },
  })

Hope it helps you