I'm building a music reporting application for a radio station using Nodejs and Sequelize. I'm working with a Mysql database that has tables for artists, albums, tracks, programs, reports, and a table for all tracks in an individual report. Db tables here:
My Sequelize models are the same as the table definitions above.
I would need to output an individual report with track names and artist names.
I tried fetching results from report__track table, that outputs the following:
The query:
try {
const report = await ReportTrack.findAll({
where: { report_id: req.params.id }
});
if (report) {
console.log('report', report);
res.json(report.map(r => r.toJSON()));
} else {
res.status(404).end();
}
} catch (exception) {
next(exception);
}
Output:
[
{
id: 2121,
track_id: 114083,
report_id: 99,
length: 281,
...
},
{
id: 2122,
track_id: 64653,
report_id: 99,
length: 300,
...
}
]
Desired result would be an array of objects that includes the names of the tracks, and the corresponding artists, something like this:
const reportWithTracksAndArtists = [
{
id: 2121,
track_id: 114083,
track_name: 'Track Name Here',
artist_name: 'Artist Name Here',
report_id: 99,
length: 281,
...
},
{
id: 2122,
track_id: 64653,
track_name: 'Another Track Name Here',
artist_name: 'Another Artist Name Here',
report_id: 99,
length: 300,
...
}
];
So I would need to loop through the results fetched from report__track, find the track_name based on track_id, and from the track table I would need to find the artist_name based on the artist_id in track table.
What would be a good way to do this?