node.js - Sequelize 创建记录交易

Question

我有三个模型:

    var Person = sequelize.define("Person", {
        no: {type: DataTypes.STRING, unique: true, allowNull: false}
    }, {
        classMethods: {
            associate: function(models){
                Person.hasMany(models.Task);
                Person.hasMany(models.Job);
            }
        }
    });

Task has field: description, and Job has field: title.

现在我尝试使用事务插入记录，但我被卡住了:

return sequalize.transaction(function (t) {
    return models.Person.create({
        no: '1221212'
    }, {transaction: t}).then(function (person){
        // I am stuck here, to insert multiple task and job
    }, {transaction: t});
}).then(function(result){

}).catch(function(err){

});

创建人员后如何插入多个任务和工作？

Answer 1

选项 1

您可以像这样创建子对象:

return sequelize.transaction(function (t) {
    return Person.create({
            no: '1221212',
            Tasks: [
                { taskName: 'task1' },
                { taskName: 'task2' }
            ]}, {
                transaction: t,
                include: [ { model: Task  } ]
            }
    );
});

我们在这里使用“包含”选项。

这是生成的 SQL:

Executing (61834d07-de9e-4b6e-aa53-9cead8157595): START TRANSACTION;
Executing (61834d07-de9e-4b6e-aa53-9cead8157595): SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Executing (61834d07-de9e-4b6e-aa53-9cead8157595): SET autocommit = 1;
Executing (61834d07-de9e-4b6e-aa53-9cead8157595): INSERT INTO "People" ("id","no") VALUES (DEFAULT,'1221212') RETURNING *;
Executing (61834d07-de9e-4b6e-aa53-9cead8157595): INSERT INTO "Tasks" ("id","taskName","PersonId") VALUES (DEFAULT,'task1',3) RETURNING *;
Executing (61834d07-de9e-4b6e-aa53-9cead8157595): INSERT INTO "Tasks" ("id","taskName","PersonId") VALUES (DEFAULT,'task2',3) RETURNING *;
Executing (61834d07-de9e-4b6e-aa53-9cead8157595): COMMIT;

选项 2

您还可以创建一个人员，然后向其批量添加任务。如果任务数量很大，这会很有用，因为它只对所有任务使用一个 INSERT 操作。

return sequelize.transaction(function (t) {
    return Person.create({
            no: '00000'
        }, {
            transaction: t,
        })
    .then(function(person) {
        return Task.bulkCreate( [
                { taskName: 't1', PersonId: person.id },
                { taskName: 't2', PersonId: person.id }
            ], {
                transaction: t
            });
    });
});

我们使用bulkCreate函数并手动设置外键字段的id。这将生成以下 SQL:

Executing (c9802841-6a22-4633-a006-41a78a9623b2): START TRANSACTION;
Executing (c9802841-6a22-4633-a006-41a78a9623b2): SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Executing (c9802841-6a22-4633-a006-41a78a9623b2): SET autocommit = 1;
Executing (c9802841-6a22-4633-a006-41a78a9623b2): INSERT INTO "People" ("id","no") VALUES (DEFAULT,'00000') RETURNING *;
Executing (c9802841-6a22-4633-a006-41a78a9623b2): INSERT INTO "Tasks" ("id","taskName","PersonId") VALUES (DEFAULT,'t1',4),(DEFAULT, 't2', 4)
Executing (c9802841-6a22-4633-a006-41a78a9623b2): COMMIT;