I have a myPlaylistSongs table, and there are Playlist_ID and Song_ID columns in it. Each row can have only one value, and the table format is as follows.
=====================
Playlist_ID | Song_ID
---------------------
1 | 5
1 | 3
1 | 8
=====================
Customers can create a playlist and add multiple songs to it, or delete multiple songs from it.
Playlist_ID is received as Path params, and Song_ID is received as req.body.
If Song_ID is received as an array here, how can I add the Song_ID corresponding to the Playlist_ID received as Path params at once?
The query is as below. DAO.js
const postSongs = async(playlistId, songId) => {
const playlist = await myDataSource.query(
`INSERT INTO myPlaylistSongs(playlist_id, song_id)
VALUES(?, ?)`,
[playlistId, songId],
);
return playlist;
};
I need help.
CodePudding user response:
Try something like this
const postSongs = async(playlistId, songId) => {
const playlist = await myDataSource.query(
`INSERT INTO myPlaylistSongs(playlist_id, song_id)
VALUES ${songId.map((sID) => "(" sID "," playlist_id ")").join(",")},
);
return playlist;
};
CodePudding user response:
You should be able to build a single query by specifying an array of (?, ?)
and for the values to be inserted:
const postSongs = async (playlistId, songId) => {
if (songId.length === 0) return []; // Empty array
let valuesString = '(?, ?),'.repeat(songId.length);
// Remove last comma
valuesString = valuesString.substring(0, values.length - 1);
const valuesArr = [];
for (const song of songId) {
valuesArr.push(playlistId);
valuesArr.push(song);
}
const playlist = await myDataSource.query(
`INSERT INTO myPlaylistSongs(playlist_id, song_id)
VALUES ${values}`,
valuesArr
);
return playlist;
};