Query with max value from multiple relationships

I know withMax() can get the value of single relationship, but i want query from multiple relationships

artists table

id | name | created_at | updated_at | deleted_at
------------------------------------------------
1  | Joe  |            |            |          |
2  | Sam  |            |            |          |

Artist many to many Music

musics table

id | name | rating | created_at | updated_at | deleted_at
---------------------------------------------------------
1  | AA   |  2     |            |            |          |
2  | BB   |  5     |            |            |          |

artist_music pivot table

artist_id | music_id |
----------------------
1         | 1        |
2         | 1        |

Artist many to many Movie

movies table

id | name | rating | created_at | updated_at | deleted_at
---------------------------------------------------------
1  | CC   |  3     |            |            |          |
2  | DD   |  1     |            |            |          |

artist_movie pivot table

artist_id | movie_id |
----------------------
1         | 1        |
2         | 2        |

as you can see, the result of each relationships

id | name | musics_max_rating
-----------------------------
1  | Joe  |       2         |
2  | Sam  |       2         |


id | name | movies_max_rating
-----------------------------
1  | Joe  |       3         |
2  | Sam  |       1         |

I want to query get the result of all Artist with max_rating like this

id | name | max_rating
----------------------
1  | Joe  |     3    |
2  | Sam  |     2    |

I write the raw query seems work, but no idea how to convert to Eloquent, OR any better way to do this ? Thank you

SELECT artists.*, greatest(ifnull(musics_max_rating, 0), ifnull(movies_max_rating, 0)) AS max_rating
FROM artists
LEFT JOIN (
    SELECT artist_id , MAX(rating) AS musics_max_rating
    FROM musics
    LEFT JOIN artist_music ON artist_music.music_id = musics.id
    WHERE musics.deleted_at is NULL
    GROUP BY artist_id
) AS mus ON artists.id = mus.artist_id
LEFT JOIN (
    SELECT artist_id , MAX(rating) AS movies_max_rating
    FROM movies
    LEFT JOIN artist_movie ON artist_movie.movie_id = movies.id
    WHERE movies.deleted_at is NULL
    GROUP BY artist_id
) AS mov ON artists.id = mov.artist_id
WHERE artists.deleted_at is NULL

kevinbui

4 years ago

Best Answer

Level 41

You can use joinSub and selectRaw to write that query.

$musicsMaxRating = Music::selectRaw('artist_id , MAX(rating) AS musics_max_rating')
    ->leftJoin('artist_music', 'artist_music.music_id', '=', 'musics.id')
    ->whereNull('musics.deleted_at')
    ->groupBy('artist_id');

$moviesMaxRating = Movie::selectRaw('artist_id , MAX(rating) AS movies_max_rating')
    ->leftJoin('artist_movie', 'artist_movie.movie_id', '=', 'movies.id')
    ->whereNull('movies.deleted_at')
    ->groupBy('artist_id');

$artists = Artist::selectRaw('artists.*, greatest(ifnull(musics_max_rating, 0), ifnull(movies_max_rating, 0)) AS max_rating')
    ->leftJoinSub($musicsMaxRating, 'mus', function ($join) {
            $join->on('artists.id', '=', 'mus.artist_id');
        })
    ->leftJoinSub($moviesMaxRating, 'mov', function ($join) {
            $join->on('artists.id', '=', 'mov.artist_id');
        })
    ->whereNull('artists.deleted_at')
    ->get();

For much better clarity, I recommend rename the musics table and its model to songs or albums.

newbie360

4 years ago

Level 24

@kevinbui

Perfect !!! You give a very good example for use leftJoinSub() and thats why i want convert to Eloquent, because it auto handle the softDelete

$musicsMaxRating = Music::selectRaw('artist_id , MAX(rating) AS musics_max_rating')
    ->leftJoin('artist_music', 'artist_music.music_id', '=', 'musics.id')
    // ->whereNull('musics.deleted_at')
    ->groupBy('artist_id');

$moviesMaxRating = Movie::selectRaw('artist_id , MAX(rating) AS movies_max_rating')
    ->leftJoin('artist_movie', 'artist_movie.movie_id', '=', 'movies.id')
    // ->whereNull('movies.deleted_at')
    ->groupBy('artist_id');

$artists = Artist::selectRaw('artists.*, greatest(ifnull(musics_max_rating, 0), ifnull(movies_max_rating, 0)) AS max_rating')
    ->leftJoinSub($musicsMaxRating, 'mus', function ($join) {
            $join->on('artists.id', '=', 'mus.artist_id');
        })
    ->leftJoinSub($moviesMaxRating, 'mov', function ($join) {
            $join->on('artists.id', '=', 'mov.artist_id');
        })
    // ->whereNull('artists.deleted_at')
    ->get();

Thank you very much

1 like

Please or to participate in this conversation.