NodeJS and MySQL: Importing CSV data with associations

user_12960148 0
Viewed 324

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.

sequelize.js node.js mysql
1 Answers

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.

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.

user_101387340
Related