使用 Sequelize 的多对多关系的简单示例

2022-01-19 00:00:00 many-to-many node.js javascript mysql sequelize.js

我正在尝试使用 Sequelize 构建表之间多对多关系的简单示例.但是，这似乎比我预期的要棘手.

I'm trying to build a simple example of many-to-many relation between tables using Sequelize. However, this seems to be way trickier than I expected.

这是我目前拥有的代码(./db.js 文件导出 Sequelize 连接实例).

This is the code I have currently (the ./db.js file exports the Sequelize connection instance).

const Sequelize = require("sequelize");
const sequelize = require("./db");

var Mentee = sequelize.define('mentee', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: Sequelize.STRING
    }
});

var Question = sequelize.define('question', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    text: {
        type: Sequelize.STRING
    }
});

var MenteeQuestion = sequelize.define('menteequestion', {
//    answer: {
//        type: Sequelize.STRING
//    }
});

// A mentee can answer several questions
Mentee.belongsToMany(Question, { as: "Questions", through: MenteeQuestion });

// And a question can be answered by several mentees
Question.belongsToMany(Mentee, { as: "Mentees", through: MenteeQuestion });

let currentQuestion = null;
Promise.all([
    Mentee.sync({ force: true })
  , Question.sync({ force: true })
  , MenteeQuestion.sync({ force: true })
]).then(() => {
    return Mentee.destroy({where: {}})
}).then(() => {
    return Question.destroy({ where: {} })
}).then(() => {
    return Question.create({
        text: "What is 42?"
    });
}).then(question => {
    currentQuestion = question;
    return Mentee.create({
        name: "Johnny"
    })
}).then(mentee => {
    console.log("Adding question");
    return mentee.addQuestion(currentQuestion);
}).then(() => {
    return MenteeQuestion.findAll({
        where: {}
      , include: [Mentee]
    })
}).then(menteeQuestions => {
    return MenteeQuestion.findAll({
        where: {
            menteeId: 1
        }
      , include: [Mentee]
    })
}).then(menteeQuestion => {
    console.log(menteeQuestion.toJSON());
}).catch(e => {
    console.error(e);
});

运行时我得到:

不能添加外键约束

我认为这是因为 id 类型——但我不知道它出现的原因以及我们如何修复它.

I think that is because of the id type—however I have no idea why it appears and how we can fix it.

前一个错误不会出现时出现的另一个错误是:

Another error which appeared when the previous one won't appear was:

执行(默认):INSERT INTO menteequestions (menteeId,questionId,createdAt,updatedAt) VALUES (2,1,'2017-03-17 06:18:01','2017-03-17 06:18:01');

Executing (default): INSERT INTO menteequestions (menteeId,questionId,createdAt,updatedAt) VALUES (2,1,'2017-03-17 06:18:01','2017-03-17 06:18:01');

错误:被指导者与被指导者问题没有关联！

Error: mentee is not associated to menteequestion!

另外，我得到的另一个错误——我认为这是因为 sync 中的 force:true——是:

Also, another error I get—I think it's because of force:true in sync—is:

如果存在mentees，则删除表；

DROP TABLE IF EXISTS mentees;

ER_ROW_IS_REFERENCED:无法删除或更新父行:外键约束失败

ER_ROW_IS_REFERENCED: Cannot delete or update a parent row: a foreign key constraint fails

如何解决这些问题?

再次，我只需要一个多对多 crud 操作的最小示例(在这种情况下只是插入和读取)，但这似乎超出了我的理解.为此苦苦挣扎了两天.

Again, I only need a minimal example of many-to-many crud operations (in this case just insert and read), but this seems to be beyond my understanding. Was struggling for two days with this.

推荐答案

迁移

我建议你使用 sequelize migrations 而不是 sync() 在每个模型上.有一个模块 - sequelize.cli 可让您轻松管理迁移和种子.它以某种方式通过在项目的 /models 目录中创建初始化文件 index.js 来强制项目结构.它假定您的所有模型定义都将在此目录中.此脚本遍历所有模型文件(每个模型定义在单独的文件中，例如 mentee.js、question.js)并执行 sequelize.import() 以便将这些模型分配给 sequelize 实例 - 这允许您稍后通过 sequelize[modelName] 访问它们，例如sequelize.question.

Migrations

I would suggest you use sequelize migrations instead doing sync() on each model. There is a module - sequelize.cli that allows you to manage migrations and seeds easily. It, in some way, forces a project structure by creating initialization file index.js inside /models directory of the project. It assummes that all your model definitions will be in this directory. This script iterates through all the model files (each model definition is in separate file e.g. mentee.js, question.js) and performs sequelize.import() in order to assign those models to the sequelize instance - this lets you access them later via sequelize[modelName] e.g. sequelize.question.

注意: 创建迁移文件时请记住时间戳字段 - createdAt、updatedAt 以及最终的 deletedAt.

Note: when creating migration files remember about timestamps fields - createdAt, updatedAt and, eventually, deletedAt.

我个人仅在运行测试时使用 sync() - 这可能分三个步骤显示

Personally I use sync() only when I run the tests - this may be shown in three steps

  1. 执行 sequelize.sync({ force: true }) 以同步所有模型
  2. 运行一些数据库seeds(也可以通过sequelize-cli来完成)，
  3. 运行测试.
  1. perform sequelize.sync({ force: true }) in order to synchronize all models
  2. run some database seeds (also can be done via sequelize-cli),
  3. run tests.

这很舒服，因为允许您在运行测试之前清理数据库，并且为了区分开发和测试，测试可以使用不同的数据库，例如project_test，以便开发数据库保持完整.

This is very comfortable because allows you to clean the database before running tests, and, in order to distinguish development from tests, tests can use different database e.g. project_test, so that the development database stays intact.

现在让我们继续讨论您的问题 - 两个模型之间的 m:n 关系.首先，由于您执行 Promise.all()，sync 的运行顺序可能与您在其中添加函数的顺序不同.为了避免这种情况，我建议你使用 Bluebird 承诺的 mapSeries 功能，Sequelize 在 sequelize.Promise 下使用和公开(这也是关于删除父行的最后一个错误的原因 - 您尝试删除从 menteequestion 引用的 mentees).

Now let's move on to your problem - m:n relation between two models. First of all, due to the fact that you perform Promise.all(), the sync can run in different order than you add the functions in it. In order to avoid this situation I suggest you use mapSeries feature of Bluebird promise, which Sequelize uses and exposes under sequelize.Promise (this is also the reason of your last error about deleting parent row - you try to delete mentees which is referenced from menteequestion).

sequelize.Promise.mapSeries([
    Mentee.sync({ force: true })
  , Question.sync({ force: true })
  , MenteeQuestion.sync({ force: true })
], (model) => { return model.destroy({ where: {} }); }).then(() => {

});

mapSeries 的第一个参数是 promise 数组，但是第二个参数是一个函数，它使用每个先前定义的 promise 的结果运行.由于 Model.sync() 会产生模型本身，因此我们可以在每次迭代时执行 model.destroy().

First parameter of mapSeries is array of promises, however the second one is a function which is run with the result of each previously defined promise. Due to the fact that Model.sync() results in the Model itself, we can perform model.destroy() at each iteration.

之后，您可以通过create() 向数据库中插入一些数据，就像在示例中一样.现在是时候修复 错误:mentee is not associated to menteequestion! 错误.发生这种情况是因为您已将 Mentee 与 Question 相关联，但 MenteeQuestion 和 Mentee(或 问题).为了解决这个问题，在 belongsToMany 之后，您可以添加

After that you can insert some data to the database via create(), just as in the example. Now time to fix the Error: mentee is not associated to menteequestion! error. It occurs because you have associated Mentee with Question but there is no association between MenteeQuestion and Mentee (or Question). In order to fix that, after belongsToMany, you can add

MenteeQuestion.belongsTo(Mentee, { foreignKey: 'menteeId' });
MenteeQuestion.belongsTo(Question, { foreignKey: 'questionId' });

现在您可以在查询 MenteeQuestion 时添加 include: [Mentee, Question].在执行 toJSON() 时，您还会遇到另一个错误，因为您执行的是返回实例数组的 findAll.你可以做 forEach()

Now you are able to add include: [Mentee, Question] when querying MenteeQuestion. You would also run on another error while doing toJSON(), because you do findAll which returns array of instances. You could do forEach()

menteeQuestions.forEach(menteeQuestion => {
    console.log(menteeQuestion.toJSON());
});

相关文章