使用序列的多对多关系的简单示例

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.

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

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

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

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);
});

When running this I get:

当我运行这个时，我得到:

Cannot add foreign key constraint

不能添加外键约束

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

我认为这是因为id类型——但是我不知道为什么会出现，以及我们如何修复它。

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

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

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

执行(默认):插入到menteeId (menteeId,questionId,createdAt,updatedAt)值(2,1，'2017-03-17 06:18:01'，'2017-03-17 06:18:01');

Error: mentee is not associated to menteequestion!

错误:mentee与menteequestion无关!

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

另外，我得到的另一个错误——我认为这是由于力:sync是正确的——是:

DROP TABLE IF EXISTS mentees;

如有学员，请填写表格;

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

er_row_is_reference:不能删除或更新父行:外键约束失败

How to solve these?

如何解决这些问题?

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.

同样，我只需要一个多对多crud操作的最小示例(在本例中，只需插入和读取)，但这似乎超出了我的理解范围。我为此挣扎了两天。

1 个解决方案

#1

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.

我建议您使用顺序迁移，而不是在每个模型上执行sync()。这里有一个模块—sequelize。cli使您能够轻松地管理迁移和种子。在某种程度上，它通过创建初始化文件索引来强制执行项目结构。项目/模型目录中的js。它假设所有的模型定义都在这个目录中。这个脚本遍历所有模型文件(每个模型定义都在单独的文件中，例如mentee。并执行sequelize.import()，以便将这些模型分配给sequelize实例——这允许您稍后通过sequelize[modelName]例如sequelize.question访问它们。

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

注意:当创建迁移文件时，要记住时间戳字段——createdAt, updatedAt，并最终删除。

Sync

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

我个人只在运行测试时才使用sync()—这可以通过三个步骤显示

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

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.

这非常方便，因为允许您在运行测试之前清理数据库，并且为了区分开发和测试，测试可以使用不同的数据库，例如project_test，以便开发数据库保持不变。

Many-to-many

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).

现在我们来看看问题——两个模型之间的m:n关系。首先，由于执行promip .all()，因此同步的运行顺序可能与添加函数的顺序不同。为了避免这种情况，我建议您使用Bluebird promise的mapSeries特性，它会在Sequelize下使用和暴露。承诺(这也是您最近一次删除父行错误的原因——您试图删除menteequestion中引用的徒弟)。

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

});

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.

mapSeries的第一个参数是承诺的数组，而第二个参数是一个函数，它是用前面定义的每个承诺的结果来运行的。由于Model.sync()会生成模型本身，所以我们可以在每次迭代中执行Model. destroy()。

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

之后，您可以通过create()向数据库插入一些数据，就像在示例中那样。现在是修正错误的时候了:mentee与menteequestion没有关联!错误。它发生的原因是，你有相关的问题，但是没有关联的问题。为了解决这个问题，在belongsToMany之后，您可以添加

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

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()

现在你可以增加包括:[学员，问题]当查询学员问题。在执行toJSON()时，您还将运行另一个错误，因为您确实找到了返回实例数组的findAll。你可以做forEach()

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