Laravel - ćwiczenie z raw

Wykonujemy ćwiczenie i wypełniamy pewne braki w wiedzy, jakie jeszcze mamy (limit, offset), uczymy się jak „pod spodem” działa metoda withAvg. Do dzieła.

Ok, najpierw coś prostego:

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

    $films = Movie::withAvg('reviews', 'rating')->take(5)->get();
    
    foreach($films as $m){
        $this->comment("Movie title: {$m->title} Reviews avg rating: {$m->reviews_avg_rating}");
    }
});

To już powinniśmy znać, ale take(5) oznacza limit 5 (daj tylko pierwsze 5 wyników). Bierzemy filmy ze średnią ocen ich recenzji.

Ok, teraz z offsetem:

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

    $films = Movie::withAvg('reviews', 'rating')->offset(5)->take(5)->get();
    
    foreach($films as $m){
        $this->comment("Movie title: {$m->title} Reviews avg rating: {$m->reviews_avg_rating}");
    }
});

W czystym PHP robiliśmy framework z paginacją, powinniśmy ogarniać o co chodzi.

Dobra, napiszmy sami sobie to withAvg w czystym SQL:

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

Subquery na zasadzie O(n^2) – tak naprawdę to nie wiem, czy w bazach danych tak samo liczy się złożoność obliczeniową, na razie jesteśmy na zasadzie rozumienia czym jest N+1, eager loading, lazy loading oraz jak działają podstawowe algorytmy indeksowania (na razie zazwyczaj będziemy używać BTREE).

Ale fakt faktem – dla każdego rekordu z selecta głównego odpalany jest subselect, to powinniśmy ogarniać. Robiliśmy już to, łatwizna, teraz tylko dodajmy limit i offset, bo tego nie robiliśmy (w czystym SQL) a to akurat bardzo łatwe:

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

Ok, fajnie, a teraz w ramach ćwiczenia odtwarzamy 1 do 1 ten select przy użyciu fasady DB:

Artisan::command('reviews-withAvg-db', function () {

    $films = DB::table('movies as m')
    ->select(DB::raw("m.*"), 
    DB::raw('(select avg(r.rating)  from `reviews` as r where m.id = r.movie_id) as `reviews_avg_rating`')
    )
    ->offset(5)
    ->limit(5)
    ->get();

    foreach($films as $m){
        $this->comment("Movie title: {$m->title} Reviews avg rating: {$m->reviews_avg_rating}");
    }
    
});

Nie wygląda to ładnie, ale działa i wykonuje identyczne query, co dowodzi, że rozumiemy co robimy. Oczywiście, DB::raw (pierwsze) nie jest potrzebne:

Artisan::command('reviews-withAvg-db', function () {

    $films = DB::table('movies as m')
    ->select("m.*", 
    DB::raw('(select avg(r.rating)  from `reviews` as r where m.id = r.movie_id) as `reviews_avg_rating`')
    )
    ->offset(5)
    ->limit(5)
    ->get();

    foreach($films as $m){
        $this->comment("Movie title: {$m->title} Reviews avg rating: {$m->reviews_avg_rating}");
    }
    
});

Byłem przekonany, że Laravel nie ogarnie, że zaaliasował sobie movies na m bez DB::raw, ale jak widać myliłem się. Wyobraźmy sobie jednak, że się nie pomyliłem i w select mamy wiele potencjalnych pól do użycia DB::raw – będziemy to db raw tak w nieskończoność wymieniać?

Nie musimy, jest selectRaw:

Artisan::command('reviews-withAvg-db2', function () {

    $films = DB::table('movies as m')
    ->selectRaw("m.*, (select avg(r.rating)  from `reviews` as r where m.id = r.movie_id) as `reviews_avg_rating`")
    ->offset(5)
    ->limit(5)
    ->get();

    foreach($films as $m){
        $this->comment("Movie title: {$m->title} Reviews avg rating: {$m->reviews_avg_rating}");
    }
    
});

Zabrakło dobrego przykładu na to selectRaw, ale idea jest taka, że jak robimy select, w którym pisalibyśmy DB::raw obok DB::raw, to po prostu używamy selectRaw.

Małe przypomnienie raw methods – selectRaw:

$orders = DB::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();

Też param binding to fajny touch dla bezpieczeństwa, choć w takich sprawach trzeba się bezwzględnie konsultować ze specjalistą w tej dziedzinie, nie brać mojego słowa, że ten binding zabezpieczy nas przed wszystkim.

Metoda whereRaw:

$orders = DB::table('orders')
                ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
                ->get();

Jak widać rawy są fajne do używania różnych funkcji, na przykład funkcja IF (w SQL Server IIF).

Ok, następny raw, czyli havingRaw:

$orders = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > ?', [2500])
                ->get();

Na orderByRaw to mamy przykład z poprzednich lekcji, bardzo dobry:

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

Mam nadzieję, że bez problemu ogarniamy, co tu się dzieje, robiliśmy już to. W głowie możemy mieć mętlik, ale tylko ucząc się ostro i mierząc się z problemami oraz czytając dokumentację ogarniemy temat. Inaczej wiecznie będzie to wszystko skomplikowane.

Laravel – ćwiczenie z raw