sql - 如何使用 Sequelize 对连接表生成查询？

Question

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

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 模型如下 -

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

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 来显示用户信息。对用户角色表的角色属性有搜索功能。如果特定用户的任何角色匹配，那么我期望用户记录包含与该用户关联的所有角色。

Answer 1

要获取与用户关联的所有角色，即使其中一个角色与查询匹配，您也需要定义另一个关联，以便可以在sequelize 语句中包含角色表两次。

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

Sequelize 声明 -

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)，可以获取某个角色匹配的用户的所有角色.