0

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:

Database tables

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?

tekos
  • 67
  • 4

0 Answers0