Laravel - subqueries

Znamy już subqueries w SQLu, pora nauczyć się je robić w Laravelu. Do dzieła.

Ok, wykonajmy ten kod w naszej bazie danych:

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

Już to robiliśmy, ale mam nadzieję, że pamiętamy. Teraz to samo zrobimy w Laravelu z fasadą DB:

Artisan::command('reviews-min-cnt', function () {

    $cnt = DB::table('reviews')
    ->select(DB::raw('count(rating) as cnt'))
    ->whereRaw('rating = (SELECT min(rating) from `reviews`)')
    ->get()->toArray();
    
    $this->comment("Reviews with min rating: {$cnt[0]->cnt}");
    
});

Nie wygląda to najpiękniej, ale działa. Ok, inny kod SQL:

SELECT * 
FROM `movies` 
WHERE director like "Prof.%";

Nieco inny patent na w zasadzie to samo, funkcja INSTR:

SELECT * 
FROM `movies` 
WHERE INSTR(director, "Prof.");

Ok, teraz użyjemy tego jako subquery:

SELECT * 
FROM `reviews`
WHERE movie_id in 
(SELECT id 
FROM `movies`
WHERE director like "Prof.%"
);

No i teraz cała magia Laravela, który wcale nie nakazuje nam robić ->get() i z odpowiednią metodą możemy mieć subquery bardzo prosto:

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

    $professors = DB::table('movies')
    ->select('id')
    ->where('director', 'like', 'Prof.%');

    $reviews = DB::table('reviews')
                    ->whereIn('movie_id', $professors)
                    ->get();

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

Mamy też metodę whereLike, warto również jej użyć:

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

    $professors = DB::table('movies')
    ->select('id')
    ->whereLike('director', 'Prof.%');

    $reviews = DB::table('reviews')
                    ->whereIn('movie_id', $professors)
                    ->get();

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

A gdybyśmy chcieli użyć funkcji INSTR, to jak w przypadku każdej funkcji w fasadzie DB musimy użyć raw, w tym wypadku whereRaw:

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

    $professors = DB::table('movies')
    ->select('id')
    ->whereRaw('INSTR(director, "Prof.")');

    $reviews = DB::table('reviews')
                    ->whereIn('movie_id', $professors)
                    ->get();

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

Mamy też metodę whereNotIn:


Artisan::command('movies-not-prof', function () {

    $professors = DB::table('movies')
    ->select('id')
    ->whereRaw('INSTR(director, "Prof.")');

    $reviews = DB::table('reviews')
                    ->whereNotIn('movie_id', $professors)
                    ->get();

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

I oczywiście nikt nie każe nam tych metod używać w subqueries:

$users = DB::table('users')
                    ->whereIn('id', [1, 2, 3])
                    ->get();


$users = DB::table('users')
                    ->whereNotIn('id', [1, 2, 3])
                    ->get();

Mam nadzieję, że nie przeraża nas fasada DB (ani SQL), Query Builder to taka sama część Laravela jak Eloquent i trzeba dobrze znać jedno i drugie (+ czysty SQL).

Laravel – subqueries