How to process data received as an array from req.body-CodePudding

I have tables a and b. Both data are created at the same time, and b receives the id of a as a FK.

Here, multiple rows can be added to table b at once, The values received as req.body are as follows.

{
    "userId": 1,
    "a1": "ccc",
    "a2": "ddd",
    "typeId": [1, 2, 3]
    "count": [60, 100, 5]
}

Is there a way to process the data received as an array in the query as shown below?

INSERT INTO data (a_id,typeId,count)
  VALUES (0,1,60), (0,2,100), (0,3,5),

I've tried the following:

DAO.js

const createRecordData = async(userId, a1, a2, typeId, count) => {
  await myDataSource.query(
    `INSERT INTO a (user_id,a1,a2)
  VALUES(?,?,?)`,
    [userId, a1, a2]
  );

  const datas = await myDataSource.query(
    `INSERT INTO data (a_id,typeId,count)
  VALUES ${typeId.map((tID) =>"("   "((SELECT LAST_INSERT_ID())"   ","   tID   ","   count.map((c) => c   ")" ).join(","))}`,
    [typeId, count]
  );
  return data;
};

But this returns an error like below:

SyntaxError: Unexpected string in JSON at position 101

=== I've tried the following.

const createRecordData = async(userId, a1, a2, typeId, count) => {
  await myDataSource.query(
    `INSERT INTO a (user_id,a1,a2)
  VALUES(?,?,?)`,
    [userId, a1, a2]
  );

const typeAndCount = typeId.map((type, index) => `((SELECT LAST_INSERT_ID()),${type},${count[index]}),`).join("");
  const datas = await myDataSource.query(
    `INSERT INTO data (a_id,typeId,count)
  VALUES ${typeAndCount}`,
    [typeId, count]
  );
  return data;
};

This will return that error.

"typeId.map is not a function"

What am I doing wrong? I need help.

CodePudding user response：

If you know that typeId and count arrays always have the same length, you can do like this. Otherwise, some other checks might be involved

const data = {
    "userId": 1,
    "a1": "ccc",
    "a2": "ddd",
    "typeId": [1, 2, 3],
    "count": [60, 100, 5]
};

const processData = (data) => {
  const {typeId, count} = data;
  const typeAndCount = typeId.map((type, index) => `(0,${type},${count[index]}),`).join("");
  return `INSERT INTO data (a_id,typeId,count) VALUES ${typeAndCount}`;
};

console.log(processData(data));