javascript 在循环内嵌套的findOrCreate上发生SequelizeForeignKeyConstraintError

我试图插入多行2相关表使用sequelize与mysql。
基于sequelize transaction cannot insert because of foreign key?，我尝试创建一个事务，如果我只执行一次承诺，它似乎可以工作，但是如果我在显示外键问题的循环上执行代码，它就失败了。
下面是代码：

const config = {
    "username": "root",
    "password": "",
    "database": "sequelize_test",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "pool": {
        "max": 1,
        "min": 0,
        "idle": 20000,
        "acquire": 20000
    }
};

const Sequelize = require('sequelize')
const sequelize = new Sequelize(config.database, config.username, config.password, config)

const Project = sequelize.define('projects', {
    id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
    },
    authorId: {
        type: Sequelize.INTEGER
    },
    name: {
        type: Sequelize.STRING
    }
})

const Author = sequelize.define('authors', {
    id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
    },
    name: {
        type: Sequelize.STRING,
    }
})

Author.hasMany(Project, {
    foreignKey: 'authorId',
    sourceKey: 'id'
})
Project.belongsTo(Author, {
    foreignKey: 'id'
})

sequelize
    .sync({
        force: true
    })
    .then(() => {

        var projectAuthors = [{
            projectName: "Proj 1",
            authorName: "Author 1"
        }, {
            projectName: "Proj 2",
            authorName: "Author 1"
        }, {
            projectName: "Proj 3",
            authorName: "Author 1"
        }, {
            projectName: "Proj 4",
            authorName: "Author 2"
        }, {
            projectName: "Proj 5",
            authorName: "Author 3"
        }];

        //Insert all the records on the array
        projectAuthors.forEach(function(project) {

            sequelize
                .transaction(function(t) {
                    //First insert the author
                    return Author.findOrCreate({
                        where: {
                            name: project.authorName
                        },
                        transaction: t

                    }).spread(function(author) {
                        //With the id obtained on the previous step, insert the project
                        return Project.findOrCreate({
                            where: {
                                name: project.projectName,
                                authorId: author.id
                            },
                            transaction: t
                        });
                    })

                });

        });
    });

- 编辑**

按照Ellebkey的建议，下面是我使用include的控制器。

sequelize
.sync({
    force: true
})
.then(() => {

    var projectAuthors = [{
        projectName: "Proj 1",
        authorName: "Author 1"
    }, {
        projectName: "Proj 2",
        authorName: "Author 1"
    }, {
        projectName: "Proj 3",
        authorName: "Author 1"
    }, {
        projectName: "Proj 4",
        authorName: "Author 2"
    }, {
        projectName: "Proj 5",
        authorName: "Author 3"
    }];

    //Insert all the records on the array
    projectAuthors.forEach(function(project) {

        sequelize
            .transaction(function(t) {
                //First insert the author
                return Project.findOrCreate({
                    where: {
                        name: project.projectName,
                        author: {
                            name: project.authorName
                        }
                    },
                    include: [{
                        association: Project.Author,
                    }],
                    transaction: t
                });

            });
    });
});

其失败原因如下：
无效值{名称："作者3 "}

- 编辑2（表格创建工作）**

多亏了Ellebkey和一些玩来玩去的人的建议，我的模型看起来像这样：

const Project = sequelize.define('projects', {
    name: {
        type: Sequelize.STRING
    }
});

const Author = sequelize.define('authors', {
    name: {
        type: Sequelize.STRING,
    }
});

Project.belongsTo(Author, { as: 'Author', foreignKey : 'authorId'});
Author.hasMany(Project, { as: 'Author', foreignKey : 'authorId'});

但是使用答案中的create（）代码，我仍然得到以下结果：
未处理的拒绝错误：无效值{名称：未定义}
顺便说一句，我正在使用续集4. 37. 6
先谢了

首先，根据我的经验，不推荐使用foreignKey作为关联，因为会混淆。使用as代替，而且你也不需要创建每个模型的id，续集可以为你做。

const Project = sequelize.define('projects', {
    name: {
        type: Sequelize.STRING
    }
})

现在，回到关联，这将在Project表中创建一个AuthorId列

Project.belongsTo(Author, { as: 'Author'})

const Author = sequelize.define('authors', {
    name: {
        type: Sequelize.STRING,
    }
})

Author.hasMany(Project, { as: 'Author'})

sequelize
    .sync({
        force: true
    })
    .then(() => {

最后，对于你的create和include，你必须根据你之前声明的方式创建你的对象，就像你用Find那样，你用Projects得到你的Author，所以每个对象应该是这样的：

var projectAuthors = [{
            name: "Author 1",
            //This must have the same syntax as you declared on your asociation
            Author: [{
                name: "Proj 1"
            },{
                name: "Proj 2"
            }]
        },{
            name: "Author 2",
            Author: [{
                name: "Proj 3"
            },{
                name: "Proj 4"
            }]
        },];

        projectAuthors.forEach(function(project) {

        sequelize
            .transaction(function(t) {
                //First insert the author
                return Author.findOrCreate({
                    where: {
                        //check this I'm not sure
                        name: project.name, //this is the Author name
                    },
                    include: [{
                        model: Project,
                        as: 'Author'
                    }],
                    transaction: t
                });

            });
        });
    });

您得到的错误是因为您从未在模型上声明authorName。

javascript 在循环内嵌套的findOrCreate上发生SequelizeForeignKeyConstraintError

1条答案

相关问题

热门标签

最新问答