使用Sequelize的多对多关系的简单示例 [英] Simple example of many-to-many relation using Sequelize

问题描述

我正在尝试使用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 file导出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);
});

运行时我得到：

无法添加外键约束

Cannot add foreign key constraint

我认为这是因为 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');

错误：受指导者与menteequestion无关！

Error: mentee is not associated to menteequestion!

此外，我得到的另一个错误 - 我认为这是因为 force：true in sync -is：

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

DROP TABLE IF EXISTS mentees ;

ER_ROW_IS_REFERENCED：不能del ete或更新父行：外键约束失败

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.

推荐答案

迁移

I建议您使用sequelize 迁移而不是 sync（ ）在每个型号上。有一个模块 - sequelize.cli ，可让您轻松管理迁移和种子。它以某种方式通过在项目的 / models 目录中创建初始化文件 index.js 来强制项目结构。它假设您的所有模型定义都在此目录中。此脚本遍历所有模型文件（每个模型定义都在单独的文件中，例如 mentee.js ， question.js ）并执行 sequelize.import（）以便将这些模型分配给续集实例 - 这使您可以稍后通过 sequelize [modelName] <访问它们/ code>例如 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. 运行一些数据库种子（也可以通过 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 可以以不同的顺序运行而不是你在其中添加功能。为了避免这种情况，我建议您使用 mapSeries 功能 Bluebird 承诺，Sequelize在<$下使用和公开c $ c> sequelize.Promise （这也是你上次删除父行的错误的原因 - 你试图删除引用的 mentees 来自 menteequestion ）。

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的结果一起运行。由于 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（）将一些数据插入数据库，就像在这个例子。现在是时候修复错误：被指导者与menteequestion没有关联！错误。之所以会发生这种情况，是因为您已将 Mentee 与问题相关联，但 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' });

现在你可以添加 include：[Mentee，Question] MenteeQuestion 时code>。在执行 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());
});

这篇关于使用Sequelize的多对多关系的简单示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！