mysql - 使用 Express 中的 Sequelize 中的方法创建检查表中存在的 ID

Question

我用 Express.js 编写了 api。我使用续集。我有一个方法创建：

exports.story = async (req, res) => {
  try {
    const userId = await authController.userId(req);
    const { type, firstTeam, secondTeam, date, place, leagueId } = req.body;
    const newEvent = {
      userId,
      type,
      firstTeam,
      secondTeam,
      date,
      place,
      leagueId,
    };

    const storeLeague = await models.Event.create(newEvent);
    return res.status(200).json(storeLeague);
  } catch (error) {
    return res.status(500).json(error);
  }
};

在这种方法中，我发送firstTeam= 1 和secondTeam= 5。这id是来自桌面团队。

接下来我有事件模型关联：

...
  Event.associate = models => {
    Event.belongsTo(models.Team, { foreignKey: 'firstTeam', as: 'teamFirst' });
    Event.belongsTo(models.Team, { foreignKey: 'secondTeam', as: 'teamSecond' });
  };
...

和团队模型助理：

...
  Team.associate = models => {
    Team.hasMany(models.Event, { foreignKey: 'eventId', as: 'event' });
  };
...

在桌队中，我有：

+----+--------+----------------+------+------+----------+---------------------+---------------------+
| id | userId |      name      | logo | type | leagueId |      createdAt      |      updatedAt      |
+----+--------+----------------+------+------+----------+---------------------+---------------------+
|  1 |      1 | Arsenal Londyn | test |    1 |        1 | 2020-02-24 09:13:23 | 2020-02-24 09:13:23 |
|  2 |      1 | Chelsea Londyn | test |    1 |        1 | 2020-02-24 09:22:23 | 2020-02-24 09:22:23 |
+----+--------+----------------+------+------+----------+---------------------+---------------------+

我如何检查是否存在 row by teams.id= 5 ？为什么 Sequelize 不返回错误？在表中teams我没有id= 5。

我正在使用 MySQL 数据库。

编辑（2020 年 2 月 26 日）：

团队迁移文件：

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Teams', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      userId: {
        allowNull: false,
        type: Sequelize.INTEGER,
      },
      name: {
        allowNull: false,
        type: Sequelize.STRING,
        unique: true,
      },
      logo: {
        type: Sequelize.STRING,
      },
      type: {
        allowNull: false,
        type: Sequelize.INTEGER,
      },
      leagueId: {
        allowNull: false,
        type: Sequelize.INTEGER,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Teams');
  },
};

事件迁移文件：

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Events', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      userId: {
        type: Sequelize.INTEGER,
      },
      type: {
        allowNull: false,
        type: Sequelize.INTEGER,
      },
      leagueId: {
        allowNull: false,
        type: Sequelize.INTEGER,
      },
      firstTeam: {
        allowNull: false,
        type: Sequelize.INTEGER,
      },
      secondTeam: {
        type: Sequelize.INTEGER,
      },
      date: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      place: {
        allowNull: false,
        type: Sequelize.STRING,
      },
      deleted: {
        type: Sequelize.BOOLEAN,
        defaultValue: false,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Events');
  },
};

团队模型文件：

module.exports = (sequelize, DataTypes) => {
  const Team = sequelize.define(
    'Team',
    {
      userId: DataTypes.INTEGER,
      name: DataTypes.STRING,
      logo: DataTypes.STRING,
      type: DataTypes.INTEGER,
      leagueId: DataTypes.INTEGER,
    },
    {},
  );
  Team.associate = models => {
    Team.hasMany(models.Event, { foreignKey: 'firstTeam', as: 'teamFirst' });
    Team.hasMany(models.Event, { foreignKey: 'secondTeam', as: 'teamSecond' });
  };
  return Team;
};

事件模型文件：

module.exports = (sequelize, DataTypes) => {
  const Event = sequelize.define(
    'Event',
    {
      userId: DataTypes.INTEGER,
      type: DataTypes.INTEGER,
      leagueId: DataTypes.INTEGER,
      firstTeam: DataTypes.INTEGER,
      secondTeam: DataTypes.INTEGER,
      date: DataTypes.DATE,
      place: DataTypes.STRING,
      deleted: DataTypes.BOOLEAN,
    },
    {},
  );
  Event.associate = models => {
    Event.belongsTo(models.Team, { foreignKey: 'firstTeam', as: 'teamFirst' });
    Event.belongsTo(models.Team, { foreignKey: 'secondTeam', as: 'teamSecond' });
  };
  return Event;
};

标签： mysqlexpresssequelize.js

Answer 1

您没有正确定义关联。如果您正确定义了关系约束，那么 SQL 查询本身就会返回错误。

我不理解您在问题中描述的数据模型，但我可以给您一个调试代码的提示。您可以通过将sync设置为 来查看 Sequelize 生成的查询true。

此外，您可以验证是否可以生成您期望的查询。

我希望它有帮助！

编辑：（03-03-2020）

我检查了由Sequelizefor modelsEvent和Team. 这些如下 -

Executing (default): CREATE TABLE IF NOT EXISTS `teams` (`id` INTEGER NOT NULL auto_increment , `user_id` INTEGER, `name` VARCHAR(255), `logo` VARCHAR(255), `type` INTEGER, `league_id` INTEGER, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): CREATE TABLE IF NOT EXISTS `events` (`id` INTEGER NOT NULL auto_increment , `user_id` INTEGER, `type` INTEGER, `league_id` INTEGER, `first_team` INTEGER, `second_team` INTEGER, `date` DATETIME, `place` VARCHAR(255), `deleted` TINYINT(1), `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`first_team`) REFERENCES `teams` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, FOREIGN KEY (`second_team`) REFERENCES `teams` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE) ENGINE=InnoDB;

看来您已经正确定义了关联。

在添加没有团队的事件时，我收到如下响应 -

{
    "isFulfilled": false,
    "isRejected": false
}

我在控制台上收到违反外键约束的错误，如下所示 -

Unhandled rejection SequelizeForeignKeyConstraintError: (conn=320, no: 1452, SQLState: 23000) Cannot add or update a child row: a foreign key constraint fails (`nagios_logs`.`events`, CONSTRAINT `events_ibfk_1` FOREIGN KEY (`first_team`) REFERENCES `teams` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE)
sql: INSERT INTO `events` (`id`,`user_id`,`type`,`league_id`,`first_team`,`second_team`,`date`,`place`,`created_at`,`updated_at`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?); - parameters:[1,1,2,1,2,'2020-03-23 18:30:00.000','New york','2020-03-03 06:51:07.109','20...]

我无法重现您在问题中描述的问题。