I've searched the web and I found this which might be related with my question but as I do not understand, I've come this to ask.
I'm trying to insert data into tables. I'm using sequelize v6 and mysql.
What I have in my database
I've 2 tables, student and township.
student table: id, name, email, township_id
township table: id, name
What I have in my associations
Student Model
Student.belongsTo(models.Township, {
foreignKey: "townshipId",
as: "township",
});
Township Model
Township.hasMany(models.Student, {
foreignKey: "townshipId",
as: "township",
});
What I have in req:
In my req, I have data like below:
{
name: 'john',
email: 'john@gmail.com',
township: 'Flank',
}
Question
Actually, I'm trying to bulk insert csv data into my database. The end-users will fill data into excel with like the above data (in the req) and save as csv file and import it to the system. The problem is the value I get from parsing csv is township name as text value, in this case Flank. But I do need to change it to integer township_id so that I can insert into students table with that township_id.
(What I have done: I grab the township name in this case Flank and run query SELECT township_id FROM townships WHERE name = 'Flank', and I get township_id back if the Flank is there in townships table. So, I can now use that township_id in inserting into students table. But I think it is very costly and another fact is that I also have another 50 fields like this.)
Therefore, my question is that how can I insert that township: 'Flank' into students table without explicitly finding township_id first (avoiding what I've done).
Thank you.
The problem is the value I get from parsing csv is township name as text value, in this case
Flank. But I do need to change it to integertownship_idso that I can insert intostudentstable with thattownship_id.
For this, instead of
INSERT INTO students (name , township, ...)
VALUES ('John' , 'Flank' , ...)
you must use
INSERT INTO students (name , township_id, ...)
SELECT 'John' , township_id, ...
FROM towns
WHERE town_name = 'Flank';
PS. I don't know how INSERT .. SELECT is performed in script/framework syntax.