node.js - 如何从 Node 和 Sequelize 中的多对多关系中检索额外的列

Question

我正在构建一个 Node 应用程序。我正在为数据库使用 Sequelize。现在，我在从多对多关系的数据透视表中检索额外的列/字段时遇到问题。

这是我的用户模型：

'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: "UserRoles",
        as: "Roles",
        foreignKey: "user_id"
      })
      User.belongsToMany(models.Team, {
        through: "UserTeams",
        as: "Teams",
        foreignKey: "user_id"
      })
    }
  };
  User.init({
    name: DataTypes.STRING,
    email: DataTypes.STRING,
    password: DataTypes.STRING,
    verifiedAt: DataTypes.DATE
  }, {
    sequelize,
    modelName: 'User',
  });
  return User;
};

这是我的团队模型：

'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: "GameTeams",
        as: "Games",
        foreignKey: "team_id"
      })
      Team.belongsToMany(models.User, {
        through: "UserTeams",
        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;
};

这是我的用户团队模型：

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class UserTeam extends Model {

    static associate(models) {
      // define association here
      UserTeam.belongsTo(models.User, {
        as: "User",
        foreignKey: "user_id"
      })
      UserTeam.belongsTo(models.Team, {
        as: "Team",
        foreignKey: "team_id"
      })
    }
  };
  UserTeam.init({
    user_id: DataTypes.INTEGER,
    team_id: DataTypes.INTEGER,
    status: DataTypes.INTEGER
  }, {
    sequelize,
    modelName: 'UserTeam',
  });
  return UserTeam;
};

如您所见，UserTeam 数据透视模型有一个名为 status 的额外字段。我正在尝试检索该字段。

我试过这个：

player = await User.findOne({
      where: {
        id: {
          [Op.eq]: player.id
        }
      },
      include: [
        {
          model: Team,
          as: "Teams"
        }
      ]
    })

但它没有给我状态栏。这给了我未定义的：

player.Teams[0].UserTeams.status

我也试过这个。

player = await User.findOne({
      where: {
        id: {
          [Op.eq]: player.id
        }
      },
      include: UserTeam
    })

但它抛出了这个错误：

SequelizeEagerLoadingError：UserTeam 未与用户关联！

如何从数据透视表中检索额外的状态列？

标签： node.jssequelize.js