MySQL to Eloquent (many-to-many)

I am trying to transform this mysql code to eloquent query with many-to-many relationship:

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

Those are my tables:
`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

On stackoverflow (http://stackoverflow.com/questions/41123931/laravel-5-3-eloquent-relationship-issue/41124432#41124432) I got the following response:

// 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 those $genre_ids to fetch the related movies as:
TheMovies::whereHas('TheGenres', function($q) use($genre_ids) {
    $q->whereIn('id', $genre_ids);
})->get();

But this results in 2 queries instead of one, and in my original mysql query it searches for at least 2 same genres occurrence.

willvincent

9 years ago

Level 54

But this results in 2 queries instead of one, and in my original mysql query it searches for at least 2 same genres occurrence.

That's how eager loading, et al.. works with eloquent. It doesn't use joins.

Given the relationships:

(Movie Model):

public function genres() {
  return $this->belongsToMany(App\Genre::class);
}

(Genre Model):

public function movies() {
  return $this->belongsToMany(App\Movie::class);
}

You'd eager load thusly:

// Load all movies with their genre(s):
$movies = Movie::with('genres')->get();

// Load all genres with their movie(s):
$genres = Genre::with('movies')->get();

Each of those would run exactly two queries.

NOTE: Since you're using different naming conventions than eloquent expects, you'll need to specify table and key names on those relationships.

1 like

Please or to participate in this conversation.