Querybuilder - Knowledge of Laravel

Wat is de querybuilder

De querybuilder van Laravel is een mechanisme om databasequery’s op te bouwen en uit te voeren via een fluente interface. Je begint met de table()-methode van de DB-facade en bouwt de query op met method chaining.

use Illuminate\Support\Facades\DB;

$users = DB::table('users')->get();

Intern wordt PDO-parameterbinding gebruikt, waardoor bescherming tegen SQL-injectie automatisch geregeld is.

De querybuilder werkt op alle databases die Laravel ondersteunt (MySQL, MariaDB, PostgreSQL, SQLite, SQL Server). Ook als je van database wisselt, kun je dezelfde code blijven gebruiken.

Wanneer querybuilder en wanneer Eloquent

Situatie	Aanbevolen
Modellen en relaties nodig	Eloquent
Complexe aggregaties of rapportages	Querybuilder
Grootschalige dataverwerking waar prestaties tellen	Querybuilder
Eenvoudige bewerkingen op bestaande tabellen	Querybuilder
Verwerking binnen migraties of seeders	Querybuilder

Data ophalen

Alle records ophalen

$users = DB::table('users')->get();

foreach ($users as $user) {
    echo $user->name;
}

get() geeft een Illuminate\Support\Collection terug. Elk record is een PHP-stdClass-object.

Eén record ophalen

// Het eerste record ophalen (null als niet gevonden)
$user = DB::table('users')->where('name', 'Taro Yamada')->first();

// Een exception gooien als niet gevonden (geeft automatisch een 404-response terug)
$user = DB::table('users')->where('name', 'Taro Yamada')->firstOrFail();

// Alleen de waarde van een specifieke kolom ophalen
$email = DB::table('users')->where('name', 'Taro Yamada')->value('email');

// Ophalen op ID
$user = DB::table('users')->find(3);

Waarden van een kolom als lijst ophalen

// Een collectie van de waarden in de e-mailkolom
$emails = DB::table('users')->pluck('email');

// Een associatieve collectie met name als sleutel en email als waarde
$emailByName = DB::table('users')->pluck('email', 'name');

Grote hoeveelheden data in delen verwerken

use Illuminate\Support\Collection;

// Per 100 records verwerken
DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
    foreach ($users as $user) {
        // Verwerking...
    }
});

// Gebruik chunkById als je tijdens het chunken updatet
DB::table('users')->where('active', false)
    ->chunkById(100, function (Collection $users) {
        foreach ($users as $user) {
            DB::table('users')
                ->where('id', $user->id)
                ->update(['active' => true]);
        }
    });

Als je tijdens het chunken records bijwerkt of verwijdert, gebruik dan chunkById() in plaats van chunk(). Bij chunk() kunnen records verschuiven.

Streamen (LazyCollection)

DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
    // Eén voor één verwerken
});

Aggregaties

$count   = DB::table('users')->count();
$maxAge  = DB::table('users')->max('age');
$minAge  = DB::table('users')->min('age');
$avgAge  = DB::table('users')->avg('age');
$total   = DB::table('orders')->sum('amount');

// Aggregeren met voorwaarden
$avgPremium = DB::table('orders')
    ->where('plan', 'premium')
    ->avg('amount');

Bestaan van records controleren

if (DB::table('orders')->where('finalized', 1)->exists()) {
    // Er bestaan records
}

if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
    // Er bestaan geen records
}

De SELECT-clausule

// De op te halen kolommen opgeven
$users = DB::table('users')
    ->select('name', 'email as user_email')
    ->get();

// Duplicaten uitsluiten
$users = DB::table('users')->distinct()->get();

// Achteraf kolommen toevoegen
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

De WHERE-clausule

Basisvoorwaarden

// Gelijkheidsvoorwaarde (= mag worden weggelaten)
$users = DB::table('users')->where('votes', 100)->get();

// Een vergelijkingsoperator opgeven
$users = DB::table('users')->where('votes', '>=', 100)->get();
$users = DB::table('users')->where('name', 'like', 'Yamada%')->get();

// Meerdere voorwaarden (AND)
$users = DB::table('users')
    ->where('status', 'active')
    ->where('age', '>', 20)
    ->get();

// OR-voorwaarden
$users = DB::table('users')
    ->where('votes', '>', 100)
    ->orWhere('name', 'Taro Yamada')
    ->get();

Voorwaarden groeperen

use Illuminate\Database\Query\Builder;

// OR-voorwaarden groeperen en combineren met AND
$users = DB::table('users')
    ->where('active', true)
    ->where(function (Builder $query) {
        $query->where('role', 'admin')
              ->orWhere('role', 'moderator');
    })
    ->get();
// WHERE active = 1 AND (role = 'admin' OR role = 'moderator')

whereIn / whereBetween / whereNull

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

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

// BETWEEN-clausule
$users = DB::table('users')
    ->whereBetween('age', [20, 40])
    ->get();

// Controle op NULL
$users = DB::table('users')->whereNull('deleted_at')->get();
$users = DB::table('users')->whereNotNull('email_verified_at')->get();

whereLike (patroonvergelijking)

// Standaard niet hoofdlettergevoelig
$users = DB::table('users')
    ->whereLike('name', '%Yamada%')
    ->get();

// Hoofdlettergevoelig
$users = DB::table('users')
    ->whereLike('name', '%Yamada%', caseSensitive: true)
    ->get();

whereAny / whereAll (dezelfde voorwaarde op meerdere kolommen)

// Minstens één van de kolommen matcht de LIKE-voorwaarde
$users = DB::table('users')
    ->where('active', true)
    ->whereAny(['name', 'email', 'bio'], 'like', '%Laravel%')
    ->get();

// Alle kolommen matchen de LIKE-voorwaarde
$posts = DB::table('posts')
    ->whereAll(['title', 'content'], 'like', '%Laravel%')
    ->get();

whereNullSafeEquals (NULL-veilige gelijkheidsvergelijking)

whereNullSafeEquals en orWhereNullSafeEquals vergelijken een kolomwaarde met een opgegeven waarde, waarbij twee NULL-waarden als gelijk worden beschouwd. Bij de gewone =-operator is NULL = NULL false, maar bij whereNullSafeEquals worden twee NULL-waarden als gelijk beoordeeld. Het komt overeen met de <=>-operator van MySQL en IS NOT DISTINCT FROM van PostgreSQL.

$lastLoginIp = $request->input('last_login_ip');

// Ook als $lastLoginIp null is, worden gebruikers met last_login_ip IS NULL opgehaald
$users = DB::table('users')
    ->whereNullSafeEquals('last_login_ip', $lastLoginIp)
    ->get();

Een gewone where('column', null) wordt vertaald naar WHERE column IS NULL, maar whereNullSafeEquals('column', $value) werkt consistent, of de gebonden waarde nu null is of niet. Bijzonder handig wanneer invoer van gebruikers null kan zijn.

JOIN

// INNER JOIN
$users = DB::table('users')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.name', 'orders.amount')
    ->get();

// LEFT JOIN
$users = DB::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->get();

// JOIN over meerdere tabellen
$users = DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.amount')
    ->get();

Subquery-JOIN

// JOIN met een subquery
$latestOrders = DB::table('orders')
    ->select('user_id', DB::raw('MAX(created_at) as last_order_at'))
    ->groupBy('user_id');

$users = DB::table('users')
    ->joinSub($latestOrders, 'latest_orders', function ($join) {
        $join->on('users.id', '=', 'latest_orders.user_id');
    })
    ->get();

Sorteren, groeperen en limiteren

// Sorteren
$users = DB::table('users')
    ->orderBy('name', 'asc')
    ->get();

// Sorteren op meerdere kolommen
$users = DB::table('users')
    ->orderBy('last_name')
    ->orderBy('first_name', 'desc')
    ->get();

// Willekeurige volgorde
$users = DB::table('users')->inRandomOrder()->get();

// Groeperen
$orders = DB::table('orders')
    ->select('status', DB::raw('COUNT(*) as count'))
    ->groupBy('status')
    ->get();

// HAVING-clausule
$orders = DB::table('orders')
    ->select('user_id', DB::raw('SUM(amount) as total'))
    ->groupBy('user_id')
    ->having('total', '>', 10000)
    ->get();

// Limit en offset
$users = DB::table('users')
    ->skip(10)   // OFFSET
    ->take(5)    // LIMIT
    ->get();

Subquery’s

// Subquery in de WHERE-clausule
$activeUsers = DB::table('users')->select('id')->where('is_active', 1);

$comments = DB::table('comments')
    ->whereIn('user_id', $activeUsers)
    ->get();

// Subquery in de SELECT-clausule
$users = DB::table('users')
    ->select('name')
    ->selectSub(function ($query) {
        $query->from('orders')
              ->selectRaw('COUNT(*)')
              ->whereColumn('orders.user_id', 'users.id');
    }, 'order_count')
    ->get();

Raw-expressies

Raw-expressies worden als SQL-string rechtstreeks in de query ingevoegd. Geef je gebruikersinvoer rechtstreeks door, dan loop je risico op SQL-injectie. Schrijf ze altijd veilig met bindings.

// DB::raw() — een willekeurige SQL-expressie invoegen
$users = DB::table('users')
    ->select(DB::raw('count(*) as user_count, status'))
    ->groupBy('status')
    ->get();

// selectRaw — een raw-expressie toevoegen aan de SELECT-clausule
$orders = DB::table('orders')
    ->selectRaw('price * ? as price_with_tax', [1.10])
    ->get();

// whereRaw — een raw-expressie toevoegen aan de WHERE-clausule
$orders = DB::table('orders')
    ->whereRaw('price > IF(state = "JP", ?, 100)', [500])
    ->get();

// havingRaw — een raw-expressie toevoegen aan de HAVING-clausule
$orders = DB::table('orders')
    ->select('department', DB::raw('SUM(amount) as total'))
    ->groupBy('department')
    ->havingRaw('SUM(amount) > ?', [100000])
    ->get();

// orderByRaw — een raw-expressie toevoegen aan de ORDER BY-clausule
$orders = DB::table('orders')
    ->orderByRaw('updated_at - created_at DESC')
    ->get();

INSERT / UPDATE / DELETE

INSERT

// Eén record invoegen
DB::table('users')->insert([
    'email' => '[email protected]',
    'name'  => 'Taro Yamada',
]);

// Meerdere records invoegen
DB::table('users')->insert([
    ['email' => '[email protected]', 'name' => 'Taro Yamada'],
    ['email' => '[email protected]', 'name' => 'Hanako Suzuki'],
]);

// Het AUTO_INCREMENT-ID ophalen na het invoegen
$id = DB::table('users')->insertGetId([
    'email' => '[email protected]',
    'name'  => 'Jiro Sato',
]);

UPSERT (INSERT OR UPDATE)

// Bijwerken als het bestaat, anders invoegen
DB::table('users')->upsert(
    [
        ['email' => '[email protected]', 'name' => 'Taro Yamada', 'votes' => 5],
        ['email' => '[email protected]', 'name' => 'Hanako Suzuki', 'votes' => 10],
    ],
    uniqueBy: ['email'],    // Kolommen voor de duplicaatcontrole
    update: ['name', 'votes'] // Kolommen die worden bijgewerkt
);

UPDATE

// Bijwerken met voorwaarden
$affected = DB::table('users')
    ->where('id', 1)
    ->update(['name' => 'Ichiro Yamada', 'updated_at' => now()]);

// Verhogen en verlagen
DB::table('users')->where('id', 1)->increment('votes');       // +1
DB::table('users')->where('id', 1)->increment('votes', 5);    // +5
DB::table('users')->where('id', 1)->decrement('votes');       // -1
DB::table('users')->where('id', 1)->decrement('balance', 100); // -100

DELETE

// Verwijderen met voorwaarden
$deleted = DB::table('users')->where('status', 'inactive')->delete();

// De hele tabel leegmaken (reset AUTO_INCREMENT)
DB::table('users')->truncate();

Voorwaardelijke query’s (when)

Wil je queryvoorwaarden dynamisch toepassen, dan schrijf je vertakkingen overzichtelijk met when().

$status = request('status');
$sortBy = request('sort', 'name');

$users = DB::table('users')
    ->when($status, function ($query, $status) {
        $query->where('status', $status);
    })
    ->when($sortBy === 'email', function ($query) {
        $query->orderBy('email');
    }, function ($query) {
        $query->orderBy('name');
    })
    ->get();

Debuggen

// De gegenereerde SQL bekijken
$sql = DB::table('users')->where('active', true)->toSql();
// "select * from `users` where `active` = ?"

// Zowel de SQL als de bindings bekijken
$bindings = DB::table('users')->where('active', true)->getBindings();

// De query uitvoeren en dumpen (uitvoering gaat door)
DB::table('users')->where('active', true)->dump();

// De query uitvoeren, dumpen en stoppen
DB::table('users')->where('active', true)->dd();

dd() is handig bij het debuggen, maar gebruik het nooit in productie. Het is veiliger om de SQL en bindings te bekijken met toSql() en getBindings().

Samenvatting

Overzicht van veelgebruikte methodes

Methode	Beschrijving
`get()`	Alle records ophalen (Collection)
`first()`	Het eerste record ophalen
`find($id)`	Eén record ophalen op ID
`value($column)`	Eén kolomwaarde ophalen
`pluck($column)`	Een lijst met kolomwaarden ophalen
`count()`	Aantal
`sum($col)`	Som
`avg($col)`	Gemiddelde
`max($col)`	Maximum
`min($col)`	Minimum
`exists()`	Bestaan controleren
`insert([...])`	Invoegen
`update([...])`	Bijwerken
`delete()`	Verwijderen
`chunk($n, fn)`	In delen verwerken
`when($cond, fn)`	Voorwaardelijke query
`toSql()`	Gegenereerde SQL bekijken

Querybuilder vs Eloquent

De querybuilder werkt op een lager niveau dan Eloquent en geeft geen modelinstanties maar stdClass terug. Heb je geen relaties of modelevents (observers) nodig, dan is de querybuilder eenvoudiger en sneller.

// Eloquent: geeft instanties van het User-model terug
$users = User::where('active', true)->get();
echo $users[0]->name; // User-object

// Querybuilder: geeft stdClass terug
$users = DB::table('users')->where('active', true)->get();
echo $users[0]->name; // stdClass-object

​Wat is de querybuilder

​Wanneer querybuilder en wanneer Eloquent

​Data ophalen

​Alle records ophalen

​Eén record ophalen

​Waarden van een kolom als lijst ophalen

​Grote hoeveelheden data in delen verwerken

​Streamen (LazyCollection)

​Aggregaties

​Bestaan van records controleren

​De SELECT-clausule

​De WHERE-clausule

​Basisvoorwaarden

​Voorwaarden groeperen

​whereIn / whereBetween / whereNull

​whereLike (patroonvergelijking)

​whereAny / whereAll (dezelfde voorwaarde op meerdere kolommen)

​whereNullSafeEquals (NULL-veilige gelijkheidsvergelijking)

​JOIN

​Subquery-JOIN

​Sorteren, groeperen en limiteren

​Subquery’s

​Raw-expressies

​INSERT / UPDATE / DELETE

​INSERT

​UPSERT (INSERT OR UPDATE)

​UPDATE

​DELETE

​Voorwaardelijke query’s (when)

​Debuggen

​Samenvatting

Wat is de querybuilder

Wanneer querybuilder en wanneer Eloquent

Data ophalen

Alle records ophalen

Eén record ophalen

Waarden van een kolom als lijst ophalen

Grote hoeveelheden data in delen verwerken

Streamen (LazyCollection)

Aggregaties

Bestaan van records controleren

De SELECT-clausule

De WHERE-clausule

Basisvoorwaarden

Voorwaarden groeperen

whereIn / whereBetween / whereNull

whereLike (patroonvergelijking)

whereAny / whereAll (dezelfde voorwaarde op meerdere kolommen)

whereNullSafeEquals (NULL-veilige gelijkheidsvergelijking)

JOIN

Subquery-JOIN

Sorteren, groeperen en limiteren

Subquery’s

Raw-expressies

INSERT / UPDATE / DELETE

INSERT

UPSERT (INSERT OR UPDATE)

UPDATE

DELETE

Voorwaardelijke query’s (when)

Debuggen

Samenvatting