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

Question

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.

You can get comma-separated string of countries through this: 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 Then you can loop through result and split that string using countries.split(',') — Sookie Singh
– Sookie Singh, Commented Jul 25, 2018 at 18:50
In my actual application, it's slightly more complicated, as I have several more tables similar to "movie_countries". Would this still be an efficient solution? I imagine the query would get quite large. — MerrickC
– MerrickC, Commented Jul 25, 2018 at 18:57
Well, there are many solutions. You can fetch all the results of every table and use lodash or similar libraries for filtering your data. — Sookie Singh
– Sookie Singh, Commented Jul 25, 2018 at 19:01
What version of SQL Server are you using? If you're using 2016+ you can have SQL Server format your result set as JSON and return a JSON object — Eli
– Eli, Commented Jul 25, 2018 at 19:52

MerrickC · Accepted Answer · 2018-07-25 20:51:19Z

1

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(',') :[];
});

edited Jul 25, 2018 at 20:51

MerrickC

1673 silver badges16 bronze badges

answered Jul 25, 2018 at 19:12

Sookie Singh

1,62313 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MerrickC Over a year ago

If my tables are large, won't fetching all the data into variables be very slow?

Sookie Singh Over a year ago

No, it won't be that slow. I don't know the exact benchmarks but I have been doing similar things on very large tables. First solution, puts more load on JS than on SQL while second solution divides load on both JS and SQL. You can check for yourself which one is fast.

MerrickC Over a year ago

This solution worked for me. Thank you! (For clarification, this solution uses Lodash)

MerrickC Over a year ago

Just a comment, the = in the lodash solution should be ===

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related