如何在 1:M 关系中使用外键，当使用 SequelizeJS 和 PostgreSQL 实现 .bulkCreate(

Question

我正在编写一个可以从(.csv(数据集中提取数据的脚本。我能够将数据拉入控制台，创建新表并将数据插入数据库。

我想弄清楚如何在实现 .bulkCreate(( 时设置外键。当我坚持使用常规 .create(( 时，我收到资源错误。

数据集保存在 2 个数组中：

• 竞争对手对象
• 遇见对象

到目前为止，这就是我尝试插入数据的方式：

sequelize.sync().then(() => {
meets.bulkCreate(meetObjs).then(data => {
competitorObjs.forEach(x => {
competitors.create({
MeetID: x.MeetID,
Name: x.Name,
Sex: x.Sex,
Equipment: x.Equipment,
Age: x.Age,
Division: x.Division,
BodyweightKg: x.BodyweightKg,
WeightClassKg: x.WeightClassKg,
BestSquatKg: x.BestSquatKg,
BestBenchKg: x.BestBenchKg,
BestDeadlift: x.BestDeadlift,
TotalKg: x.TotalKg,
Place: x.Place,
Wilks: x.Wilks,
UserId: data.get("MeetID")   // Set FK here (Not sure if correct implementation)
})
})
}).then(() => {
console.log("Bulk Creation Success!");
}).catch((err) => {
console.log(err)});
})

脚本完成后，只有"满足"表已填充，但"竞争对手"保持空。

如何在每个"竞争对手"插入中设置外键以指向"满足"表中的每个主键？(FK 未设置为唯一(

Answer 1

你快到了。您可以使用index

循环

competitorObjs.forEach((x, index) => {
competitors.create({
...
..
..
UserId: data[index]["MeetID"]

这种方法的问题在于，要创建 N 个对象，您将创建 N + 1 个查询。 1 表示批量创建，N 表示关联表。您也可以在关联的数组中使用bulkCreate。

您可以遍历competitorObjs，如上所述填写UserId，然后执行批量创建。

Answer 2

发布解决方案或至少让我填充竞争对手表的内容。

出于某种原因，我认为我们在插入过程中设置了外键。

在顶部，我想我将"竞争对手"模型中的FK设置为"MeetID">

meets.hasMany(competitors, {
foreignKey: "MeetID",
constraints: false
});
var meetObjs = [];
var competitorObjs = [];
//  Extract meets data to meetObjs
//  confirm connection...
sequelize.authenticate().then(() => {
console.log("Data base connection established")
}).then(() => {
//  read contents of file...
fs.readFile(path.join(__dirname, "/data/meets.csv"), "utf-8", (err, data) => {
if (err) {
console.log(err);
} else {
//  begin parsing data...
Papa.parse(data, {
delimiter: ',',
newline: 'n',
complete: function (results) {
//  push to meetObjs array
for (let i = 1; i < results.data.length - 1; i++) {   //NOTE: Last element extracted is NaN. Look more into this.
meetObjs.push({
MeetID: results.data[i][0],
MeetPath: results.data[i][1],
Federation: results.data[i][2],
Date: results.data[i][3],
MeetCountry: results.data[i][4],
MeetState: results.data[i][5],
MeetTown: results.data[i][6],
MeetName: results.data[i][7]
})
}


//  read contents of file...
fs.readFile(path.join(__dirname, "/data/openpowerlifting.csv"), "utf-8", (err, data) => {
if (err) {
console.log(err);
} else {
//  begin parsing data...
Papa.parse(data, {
delimiter: ',',
newline: 'n',
complete: function (results) {
//  push to meetObjs array
for (let i = 1; i < results.data.length - 1; i++) {   //NOTE: Last element extracted is NaN. Look more into this.
competitorObjs.push({
MeetID: Number(results.data[i][0]),
Name: results.data[i][1],
Sex: results.data[i][2],
Equipment: results.data[i][3],
Age: Number(results.data[i][4]),
Division: results.data[i][5],
BodyweightKg: Number(results.data[i][6]),
WeightClassKg: results.data[i][7],
Squat4Kg: Number(results.data[i][8]),
BestSquatKg: Number(results.data[i][9]),
Bench4Kg: Number(results.data[i][10]),
BestBenchKg: Number(results.data[i][11]),
Deadlift4Kg: Number(results.data[i][12]),
BestDeadlift: Number(results.data[i][13]),
TotalKg: Number(results.data[i][14]),
Place: results.data[i][15],
Wilks: Number(results.data[i][16])
})
}
sequelize.sync().then(() => {
meets.bulkCreate(meetObjs).then(() => {
competitors.bulkCreate(competitorObjs).then(() => {
console.log("Competitors created!");
}).catch("Competitors creation failed!")
}).then(() => {
console.log("Meets created!");
}).catch(() => {
console.log("Meets creation failed!");
})
})
}
});
}
});
}
});
}
});

}).catch(() => {
console.log("Something went wrong trying to connect to the DB");
});