Laravel - SQL having

Uczymy się czym jest having i jak go używać w SQLu oraz Laravelu. Kontynuacja lekcji poprzednich. Do dzieła.

Ok, co nam robi ten SQL?

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

Pokazuje movie_id i jego średnią ocen. Było w lekcji o group by. Teraz w Laravelu, fasada DB:

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

    $reviews = DB::table('reviews')
    ->select('movie_id', DB::raw('avg(rating) as avg_rating'))
    ->groupBy('movie_id')
    ->get();
    foreach($reviews as $r){
        $this->comment("Movie id: {$r->movie_id} Avg rating: {$r->avg_rating}");
    }
});

Z DB::raw już pracowaliśmy. Ok, teraz coś nowego:

SELECT avg(rating) as "avg rating", 
movie_id 
from `reviews` 
GROUP BY movie_id 
HAVING AVG(rating) > 2;

Też nam pokazuje movie id i jego średnią ocen, ale tylko jeśli ta średnia jest większa niż 2. Having to taki warunek do group by, where tam nie działa (choć having można używać też tam gdzie where, ale to trochę bez sensu i komplikuje logikę czym jest having a czym where).

Ok, w Laravelu to samo:

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

    $reviews = DB::table('reviews')
    ->select('movie_id', DB::raw('avg(rating) as avg_rating'))
    ->groupBy('movie_id')
    ->havingRaw('avg(rating) > ?', [2])
    ->get();
    
    foreach($reviews as $r){
        $this->comment("Movie id: {$r->movie_id} Avg rating: {$r->avg_rating}");
    }
});

Czyli havingRaw było potrzebne, inaczej nie dało się funkcji SQL tam wstawić. Ok, rozwińmy nasze query:

SELECT avg(rating) as "avg rating", 
movie_id 
from `reviews` 
GROUP BY movie_id 
HAVING AVG(rating) > 2
ORDER BY avg(rating);

Mamy to samo, ale w szeregu od najmniejszej do największej średniej ilości ocen. Teraz to samo w Laravelu:

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

    $reviews = DB::table('reviews')
    ->select('movie_id', DB::raw('avg(rating) as avg_rating'))
    ->groupBy('movie_id')
    ->havingRaw('avg(rating) > ?', [2])
    ->orderByRaw('avg(rating)')
    ->get();

    foreach($reviews as $r){
        $this->comment("Movie id: {$r->movie_id} Avg rating: {$r->avg_rating}");
    }
});

Czyli orderByRaw, inaczej się nie da. Ok, teraz coś z withCount, do których też having można dopiąć:

Artisan::command('movies-having', function () {

    $films = Movie::withCount('reviews')->having('reviews_count', ">", 3)->get();
    
    
    foreach($films as $m){
        $this->comment("Movie title: {$m->title} Reviews: {$m->reviews_count}");
    }
});

Zaciąga film i ilość recenzji, ale tylko te filmy, które mają więcej niż 3 recenzje. Możemy get zamienić na ->dd() (czasem lepsze niż ->toSql(), pokazuje binding) i zobaczyć co my tam dostaliśmy:

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

Laravelowy SQL jest mało czytelny, więc polecam znając już ogólną filozofię po prostu odtworzyć go samemu, z palca, ale czytelnie:

select m.*, 
(
select count(*) 
from `reviews` as r 
where m.id = r.movie_id
) as `reviews_count`
from `movies` as m 
having `reviews_count` > 3

Tutaj mamy having z sub-selectem, też już wrażenia to na nas nie powinno robić. Widzimy także jak działa withCount w czystym SQL.

Laravel – SQL having