Laravel - withAvg, sum, min, max, count, exists

Poznajemy funkcje Laravela, bez których trudno sobie dziś wyobrazić życie. Do dzieła.

Ok, najpierw withCount:

Artisan::command('movies-with-count', function () {

    $movies = Movie::withCount('reviews')->get();
    foreach($movies as $movie){
        $this->comment("Movie title: {$movie->title}");
        $this->comment("Movie length: {$movie->length}");
        $this->comment("Number of reviews: {$movie->reviews_count}");
    }
});

Dodaje nam ono pole reviews_count, zawierające ilość recenzji. Ciekawostka – można zaciągać wiele countów jednym wywołaniem, przekazując tablicę:

$users = User::withCount(['posts', 'comments'])->get();

Teraz withExists, już to robiliśmy, proste:

Artisan::command('movies-with-exists', function () {

    $movies = Movie::withExists('reviews')->get();
    foreach($movies as $movie){
        $this->comment("Movie title: {$movie->title}");
        $this->comment("Movie length: {$movie->length}");
        if($movie->reviews_exists)
            $this->comment("Has reviews: true");
        else
            $this->comment("Has reviews: false");
    }
});

Teraz withAvg, funkcja agregująca i musimy podać średnią czego ma wyliczać, czyli nazwa relacji i nazwa pola (kolumny):

Artisan::command('movies-with-avg', function () {

    $movies = Movie::withAvg('reviews', 'rating')->get();
    foreach($movies as $movie){
        $this->comment("Movie title: {$movie->title}");
        $this->comment("Movie length: {$movie->length}");
        $this->comment("Reviews avg rating: {$movie->reviews_avg_rating}");
    }
});

Pozostałe funkcje SQL, czyli min, max i sum też mają swój odpowiednik:

Artisan::command('movies-with-minmax', function () {

    $movies = Movie::withSum('reviews', 'rating')
    ->withMin('reviews','rating')
    ->withMax('reviews', 'rating')
    ->get();
    
    foreach($movies as $movie){
        $this->comment("Movie title: {$movie->title}");
        $this->comment("Reviews - min rating: {$movie->reviews_min_rating}");
        $this->comment("Reviews - max rating: {$movie->reviews_max_rating}");
        $this->comment("Reviews - sum of all ratings: {$movie->reviews_sum_rating}");
    }
});

Ok, ale jak to wszystko działa? Zdebugujmy:

Artisan::command('movies-with-count', function () {

    $m = Movie::withCount('reviews')->dd();
    $this->comment("Min rating:");
    
});

To dostaniemy:

select `movies`.*, 
(select count(*) from `reviews` where `movies`.`id` = `reviews`.`movie_id`) as `reviews_count` 
from `movies`

I to jest to, o czym mówiłem dawno temu, czyli selecty z subqueries są wykonywane dla każdego rekordu z selecta rodzica, taka jakby zagnieżdżona pętla.

Ok, sprawdźmy w sql czy zadziała. Następnie piszemy dla withAvg:

select `movies`.*, 
(select avg(`reviews`.`rating`) from `reviews` where `movies`.`id` = `reviews`.`movie_id`) as `reviews_avg_rating` 
from `movies`;

I to jest ta pętla w pętli. Każdy rekord z głównego selekta ma swoje id i dla każdego id jest selekt z sub-query odpalany, inaczej by to wszystko nie było w stanie zadziałać.

Swoją drogą nienawidzę sposobu, w jaki Laravel buduje te queries. Wolę sobie nazwy tabel poaliasować, wtedy jest (moim zdaniem) dużo czytelniej:

select m.*, 
(select avg(r.rating) 
from `reviews` as r 
where m.id = r.movie_id) as `reviews_avg_rating` 
from `movies` as m;

Mam nadzieję, że na tym poziomie te subqueries i inne bzdety nie robią na nas specjalnego wrażenia. Inaczej trudno pójść dalej z materiałem, jeżeli SQL będzie nas przerastać.

PS. Teraz powinniśmy umieć napisać czystym SQL withMin, withMax, withSum. Przerastać nas może withExists, już pokazuję, jak do tego podejść:

select m.*,
exists(
select * 
from `reviews` as r 
where m.id = r.movie_id
) as `reviews_exists` 
from `movies` as m;

Funkcja exists, kolejna funkcja SQL, którą warto zapamiętać.

Laravel – withAvg, sum, min, max, count, exists