Laravel (5.3) Eloquent - relationship problem

Question

I have the following 3 tables that are normalized:

`Table: TheMovies` id | MovieName --------------------- 1 | Zootopia 2 | Moana 3 | Toy Story `Table: TheGenres` id | GenreName --------------------- 21 | Action 22 | Animation 23 | Adventure `Table: mMoviesGenres` movieID | genreID --------------------- 1 | 21 1 | 23 2 | 22 2 | 21 3 | 23 3 | 21 

As you can see in the third table, the film has several genres, and the genre has several films.

I created TheMovies and TheGenres models in laravel.

I made sure that the relationships are made inside the models using the following code:

 class TheMovies extends Model { public function TheGenres() { return $this->belongsToMany('App\TheGenres', 'mMoviesGenres', 'seriesID', 'genreID'); } } class TheGenres extends Model { public function TheGenres() { return $this->belongsToMany('App\TheMovies', 'mMoviesGenres', 'genreID', 'seriesID'); } } 

I tested everything and I was able to display a list of genres for a particular movie, and I also managed to display a list of films for a specific genre.

The actual problem is that I want to display related movies for a specific movie based on the genre.

Take TheMovies.id = 1, which is similar to TheMovies.id = 3, they are both Action and Adventure, as you can see in the third table.

I figured out the query that is needed based on the following message: SQL Query based on another table .

 SELECT m2.movieId FROM mMoviesGenres m1 INNER JOIN mMoviesGenres m2 ON m1.genreID = m2.genreID WHERE m1.movieId = 1 AND m2.movieId <> 1 GROUP BY m2.movieId HAVING COUNT(*) >= 2 

But I don’t know how to convert this request to Eloquent style, and yes, I can make a raw request to Eloquent, but I want to use the created relationship.

Please give me some advice.

Answer 1

You can try:

 // returns array of genre_ids associate with the TheMovies.id => 1 $genre_ids = TheGenres::whereHas('TheMovies', function($q) { $q->where('id', 1); })->pluck('id')->toArray(); 

Then use these $genre_ids to select related movies as follows:

 TheMovies::whereHas('TheGenres', function($q) use($genre_ids) { $q->whereIn('id', $genre_ids); })->get(); 

Update

Assuming you have:

 $genre_ids = [21, 23]; 

then your request could be as follows:

 TheMovies::whereHas('TheGenres', function($q) use($genre_ids) { $q->whereIn('genreID', $genre_ids) ->groupBy('movieID') ->havingRaw('COUNT(DISTINCT genreID) = 2'); })->get(); 

Note. I have not tested it, but you can try.