Laravel - sql group by

Poznajemy group by i agregowanie po grupach w SQL. Kontynuacja lekcji poprzednich. Do dzieła!

Ok, małe przypomnienie tego i owego. Przypomnijmy sobie tę komendę:

Artisan::command('review-min-rating-2', function () {

    $min = Review::min('rating');
    
    $numberOfMinReviews = Review::where('rating', $min)->count();

    $this->comment("Minimum rating: {$min}");
    $this->comment("Number of reviews with minimal rating: {$numberOfMinReviews }");
    
});

Czyli nasz pierwszy min na całej tabeli. Odpowiednik:

SELECT min(rating) 
from `reviews`;

Warto zwrócić uwagę, że tutaj dostajemy jedną odpowiedź. Ok, nasz pierwszy sub-select:

SELECT COUNT(rating)
from `reviews`
where rating = (SELECT min(rating) from `reviews`);

Oraz podejście ze zmiennymi SQL:

SELECT @min_rating := min(rating) from `reviews`;

SELECT count(*) 
from `reviews`
 where rating = @min_rating;

Nasz pierwszy max (Eloquent oraz fasada DB):

Artisan::command('review-max-rating', function () {

    $max = Review::max('rating');
    $this->comment("Max rating: {$max}");
    
});


Artisan::command('review-max-rating-db', function () {

    $max = DB::table('reviews')->max('rating');
    $this->comment("Max rating: {$max}");
    
});

A tutaj funkcje with (nie wszystkie):

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}");
    }
});

A tutaj select, który zaciąga wszystko z movies i sub select, który liczy średnią z recenzji, ale tylko dla każdego filmu i dla każdego filmu daje kolumnę avg_rating:

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

I jeszcze raz przypominam – to jest O(n^2), to jest pętla w pętli, dla każdego movie leci osobny sub-select, z innym m.id, które jest porównywane z r.movie_id i tylko te są liczone, tak ta magia działa, że dostajemy do każdego filmu reviews_avg_rating.

Tam można dać sum, min, max, count(*), dopiero withExists wymaga odrobinę więcej myślenia i znajomości SQL:

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

Ok, ale teraz podejdziemy troszkę od innej strony. Od strony tabeli reviews i spróbujemy wyliczyć średnią:

SELECT avg(rating) from `reviews`;

Ok, fajnie, zrobiliśmy:

$avg = Review::avg('rating');

No, niby co to ma być? Nic wielkiego, umiemy to. Ok, to teraz taki kod:

SELECT avg(rating), movie_id from `reviews`;

Ok, myślałem, że będzie błąd, dostaliśmy jeden rekord, id 1, jakaś średnia (pewnie dla wszystkich rekordów). Sprawdźmy to raz jeszcze:

SELECT avg(rating), movie_id from `reviews`; 

SELECT avg(rating) from `reviews` WHERE movie_id = 1;

Ok, dostajemy dwa result sety, drugi pokazuje prawdziwą średnią dla recenzji o id 1, pierwszy jest nieco popsuty, pokazuje średnią dla wszystkich ocen wszystkich filmów plus id 1.

Generalnie pomyślmy o co nam chodzi w pierwszym przypadku. Chodzi nam o to, aby podać średnią ocen dla każdego filmu. A zatem musimy jakoś te poagregowane dane… pogrupować.

Tak:

SELECT avg(rating), movie_id 
from `reviews` 
GROUP BY movie_id;

Teraz już wszystko działa jak należy. Dostajemy średnią ocen dla każdego filmu z osobna.

Możemy też policzyć sumę wszystkich ocen dla każdego z filmów:

SELECT sum(rating), movie_id
from `reviews` 
GROUP BY movie_id;

Możemy też więcej wyciągnąć funkcji agregujących, ważne aby grupowanie się zgadzało:

SELECT sum(rating),
avg(rating),
min(rating),
max(rating),
count(*),
movie_id 
from `reviews` 
GROUP BY movie_id;

Możemy to sobie poaliasować, tak czy inaczej wyciągnęliśmy dla każdego filmu średnią ocen, minimalną ocenę, maksymalną ocenę, sumę ocen oraz ilość recenzji.

A jak to robić w Laravelu? Ciężko jest. Eloquent odpada, bo tutaj nie pracujemy na modelu, tylko dziwnie poagregowanych danych. No odpada, pomyślmy chwilę, to sami do tego dojdziemy.

Fasada DB? Tak, ale z DB::raw:

$user_info = DB::table('usermetas')
                 ->select('browser', DB::raw('count(*) as total'))
                 ->groupBy('browser')
                 ->get();

Oczywiście dla chcącego nic trudnego i DB raw możemy się pozbyć z pewną paczką:

composer require tpetry/laravel-query-expressions

Nie będziemy tego teraz tłumaczyć, jeszcze to sobie zrobimy, ale dobra znajomość SQL była tu priorytetem. Mam nadzieję, że pewne rzeczy się rozjaśniają, zwłaszcza że SQL to temat rzeka i w dodatku bardzo ciekawy i tylko te początki są trudne, jak już to konceptualnie ogarniemy to później jest magia.

Laravel – sql group by