CODEWITHSUNDEEP
javascriptsql-servernode.jsnode-mssql
MerrickC
MerrickC

Reputation: 157

Child query for each row in Parent query - Node.js and node-mssql

I am trying to figure out the most efficient way to do the following using Node.JS and node-mssql. I want an end result that looks like this:

{
   movie_id: '1234',
   name: 'Hereditary',
   countries: [
      "Canada",
      "United States",
      "Australia"
  ]
},
{
   movie_id: '1235',
   name: 'Rosemarys Baby',
   countries: [
      "Canada"
  ]
}

My tables look like this

movies:

movie_id    | name 
---------------------
1234        | Hereditary
1235        | Rosemarys Baby

movie_countries:

id  |  movie_id    | country
---------------------
1   |  1234        | Canada
2   |  1234        | United States
3   |  1234        | Australia
4   |  1235        | Canada

So far, I've tried several of the different examples from mssql's Github page. I am able to get the results from the "movies" table, but I'm getting stuck when trying to get the countries for each movie.

Upvotes: 0

Views: 415

Answers (1)

Sookie Singh
Sookie Singh

Reputation: 1623

Fetch all the data of movies table and movie_countries table in respective variables.

movies.forEach(function(movie) {
    movie.countries = _.remove(movie_countries, function(mc) {
        return movie.movie_id === mc.movie_id; //Edited this from = to ===
    });
});

Sql Way: SELECT mv.movie_id, mv.name, GROUP_CONCAT(mvc.country) AS countries FROM movies mv LEFT JOIN movie_countries mvc ON mv.movie_id = mvc.movie_id WHERE 1 GROUP BY mv.movie_id

movies.forEach(function(movie) {
    movie.countries = movies.countries.length ? movies.countries.split(',') :[];
});

Upvotes: 1

Related Questions