如何使用 Sequelize 在连接表上生成查询? [英] How to generate query on join table using Sequelize?

问题描述

假设有两个表，即用户和用户角色.用户和用户角色之间是一对多的关系.用户的续集模型如下 -

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 关联定义如下 -

Sequelize association is defined as follows -

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

我想使用

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; 

我正在开发一个 API，前端网格将使用该 API 来显示用户信息.用户角色表的角色属性有搜索功能.如果匹配特定用户的任何角色，那么我希望有一个用户记录，其中包含与该用户关联的所有角色.

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.

推荐答案

要获取与用户关联的所有角色，即使其中一个与查询匹配，您需要定义另一个关联，以便您可以包含角色表两次在续集声明中.

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 声明 -

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,
});

借助第一个include(join)，您可以根据用户角色过滤用户记录，借助第二个include(join)，您可以获得其中一个角色匹配的用户的所有角色.

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.

这篇关于如何使用 Sequelize 在连接表上生成查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！