node.js - 多对多关系的数据透视表上的Node JS Sequelize where子句正在获取所有记录

Question

我正在构建一个 Node JS 应用程序。我正在为数据库使用 Sequelize。现在，我在多对多关系中使用数据透视表上的 where 子句时遇到问题。

我有带有以下代码的游戏模型：

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Game extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
      Game.belongsToMany(models.Team, {
        through: models.GameTeam,
        as: "Teams",
        foreignKey: "game_id"
      })
    }
  };
  Game.init({
    title: DataTypes.STRING,
    registration_deadline: DataTypes.DATE,
    start_at: DataTypes.DATE,
    end_at: DataTypes.DATE,
    location: DataTypes.STRING,
    latitude: DataTypes.FLOAT,
    longitude: DataTypes.FLOAT
  }, {
    sequelize,
    modelName: 'Game',
  });
  return Game;
};

然后我得到了另一个模型，使用以下代码的团队。

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Team extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
      Team.belongsToMany(models.Game, {
        through: models.GameTeam,
        as: "Games",
        foreignKey: "team_id"
      })
      Team.belongsToMany(models.User, {
        through: models.UserTeam,
        as: "Users",
        foreignKey: "team_id"
      })
    }
  };
  Team.init({
    name: DataTypes.STRING,
    status: DataTypes.INTEGER,
    game_id: DataTypes.INTEGER,
    creator_id: DataTypes.INTEGER
  }, {
    sequelize,
    modelName: 'Team',
  });
  return Team;
};

如您所见，游戏和团队具有多对多的关系。

然后我得到另一个名为 User 的模型，代码如下。

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class User extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
      User.hasMany(models.VerificationToken, { foreignKey: 'userId', as: "verificationTokens", onDelete: 'cascade' });
      User.belongsToMany(models.Role, {
        through: models.UserRole,
        as: "Roles",
        foreignKey: "user_id"
      })
      User.belongsToMany(models.Team, {
        through: models.UserTeam,
        as: "Teams",
        foreignKey: "user_id"
      })
    }
  };
  User.init({
    name: DataTypes.STRING,
    email: DataTypes.STRING,
    password: DataTypes.STRING,
    verifiedAt: DataTypes.DATE
  }, {
    sequelize,
    modelName: 'User',
  });
  return User;
};

如您所见，用户和团队也具有多对多关系。现在，我正在尝试通过团队获取与用户有关系的所有游戏。以下是我的查询/代码。

let user = await User.findOne({
        where: {
          id: {
            [Op.eq]: user_id
          }
        }
      })

      let playerTeams = await user.getTeams();

      if (playerTeams.length < 1) {
        return {
          error: false,
          data: [ ]
        }
      }

     let games = await Game.findAll({
        where: where,
        order: order,
        include: [
          {
            model: Team,
            as: "Teams",
            through: {
              where: {
                team_id: {
                  [Op.in]: playerTeams.map(item => {
                    return item.id
                  })
                }
              }
            }
          }
        ]
      })

当我运行代码时，它会获取所有游戏，无论它们属于哪个用户。我的代码有什么问题，我该如何解决？

标签： node.jssequelize.js