时间戳

首页 > 解决方案 > 如何使用 Sequelize 在连接表上生成查询？

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

标签： sqlnode.jssequelize.js

解决方案

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

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

续集声明 -

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

在第一个包含（join）的帮助下，您可以根据用户角色过滤用户记录，在第二个包含（join）的帮助下，您可以获取其中一个角色匹配的用户的所有角色。

推荐阅读