Laravel(5.3)雄辩-关系问题

问题描述

我有以下3个已归一化的表:

I have the following 3 tables which are normalised:

`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 3rd table a movie has multiple genres, and a genre has multiple movies.

我已经在laravel中创建了TheMovies和TheGenres模型.

I've created TheMovies and TheGenres models in laravel.

我确保使用以下代码在模型内部建立关系:

I made sure that the relationship is 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've tested everything, and I succeeded displaying a list of genres for a particular movie, and I also succeeded displaying a list of movies for a particular genre.

实际的问题是我想根据类型显示特定电影的相关电影.

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

让我们以TheMovies.id = 1(与TheMovies.id = 3相似)为例，您可以在第三张表中看到它们既是动作游戏又是冒险游戏.

Let's take TheMovies.id = 1 which is similar with TheMovies.id = 3, they are both Action and Adventure as you can see in the third table.

我已经根据以下帖子找到了所需的查询:基于其他表的SQL查询.

I've found out the query which is needed based on the following post:SQL Query based on other 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

但是我不知道如何以Eloquent样式转换此查询，是的，我可以在Eloquent中进行原始查询，但是我想利用创建的关系.

But I don't know how to transform this query in Eloquent style, and yes I can make a raw query in Eloquent, but I want to make use of the relationship created.

请给我一些建议.

推荐答案

您可以尝试以下方式:

// 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();

然后使用那些$genre_ids来获取相关电影，如下所示:

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();

更新

假设您拥有:

$genre_ids = [21, 23];

然后您的查询可以是:

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

注意-我尚未对其进行测试，但是您可以尝试一下.

这篇关于Laravel(5.3)雄辩-关系问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！