Saturday, 10 April 2021

Inner joins 2 tables in mongoose

I'm trying to do an inner join that matches, but for some reason I get a left join. I have 2 relations tables, and I want to get the movie with the genre names.

Consider the following models:

// Movie
const MovieSchema = new mongoose.Schema({
    id: {
        type: Number,
        default: null,
        required: true,
        unique: true
    },
    title: {
        type: String,
        default: null,
        required: true,
        unique: true,
        trim: true
    },
});
const Movie = mongoose.model('Movie', MovieSchema);
module.exports = Movie;

// Genre
const GenreSchema = new mongoose.Schema({
    id: {
        type: Number,
        default: null,
        required: true,
        unique: true
    },
    name: {
        type: String,
        default: null,
        required: false,
        trim: true,
        unique: true
    }
});
const Genre = mongoose.model('Genre', GenreSchema);
module.exports = Genre;

// MovieGenre
const MovieGenreSchema = new mongoose.Schema({
    genreId: {
        type: Number,
        default: null,
        required: true
    },
    movieId: {
        type: Number,
        default: null,
        required: true
    }
});
const MovieGenre = mongoose.model('MovieGenre', MovieGenreSchema);
module.exports = MovieGenre;

I try to do the following query:

    {
        $lookup:
        {
            from: MovieGenre.collection.name,
            localField: 'id',
            foreignField: 'movieId',
            as: 'movieGenres'
        }
    },
    {
        $lookup: {
            from: Genre.collection.name,
            localField: 'g.id',
            foreignField: 'genreId',
            as: 'genreNames'
        }
    },
    {
        $match: {
            'genreNames.name': 'Action'
        }
    }

and I get the results:

{
  _id: 5ee9b51609f44c0f38262c94,
  id: 26583,
  title: 'The Keeper',
  __v: 0,
  movieGenres: [
    {
      _id: 5ee8b8cf0d186c20b4bf3ccd,
      genreId: 28,
      movieId: 26583,
      __v: 0
    },
    {
      _id: 5ee8b8cf0d186c20b4bf3cce,
      genreId: 53,
      movieId: 26583,
      __v: 0
    }
  ],
  genreNames: [
    { _id: 5ee8b68f0d186c20b4b03a3d, id: 35, name: 'Comedy', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a3e, id: 80, name: 'Crime', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a40, id: 18, name: 'Drama', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a42, id: 53, name: 'Thriller', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a43, id: 28, name: 'Action', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a45, id: 14, name: 'Fantasy', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a46, id: 27, name: 'Horror', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a4a, id: 10752, name: 'War', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a4b, id: 10402, name: 'Music', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a4c, id: 37, name: 'Western', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a4d, id: 36, name: 'History', __v: 0 }
  ]
}

but what i expected to get is:

{
  _id: 5ee9b51609f44c0f38262c94,
  id: 26583,
  title: 'The Keeper',
  __v: 0,
  movieGenres: [
    {
      _id: 5ee8b8cf0d186c20b4bf3ccd,
      genreId: 28,
      movieId: 26583,
      __v: 0
    },
    {
      _id: 5ee8b8cf0d186c20b4bf3cce,
      genreId: 53,
      movieId: 26583,
      __v: 0
    }
  ],
  genreNames: [
    { _id: 5ee8b68f0d186c20b4b03a43, id: 28, name: 'Action', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a42, id: 53, name: 'Thriller', __v: 0 },
  ]
}

Can you please tell me, What am I doing wrong? Thanks.



from Inner joins 2 tables in mongoose

No comments:

Post a Comment