Laravel Query Builder

Laravel Query Builder

It can be used to perform most database operations in your application and works on all supported database systems.

Laravel's database query builder provides a convenient, fluent interface to creating and running database queries.

Uses PDO parameter binding to protect your application against SQL injection attacks.

Retrieving Results

Selects

Raw Expressions

$users = DB::table('users')
                     ->select(DB::raw('count(*) as user_count, status'))
                     ->where('status', '<>', 1)
                     ->groupBy('status')
                     ->get();

Joins

Unions

$first = DB::table('users')
            ->whereNull('first_name');

$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

Where Clauses

Ordering, Grouping, Limit, & Offset

Conditional Clauses

$role = $request->input('role');

$users = DB::table('users')
                ->when($role, function ($query, $role) {
                    return $query->where('role_id', $role);
                })
                ->get();
$sortBy = null;

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

Inserts

DB::table('users')->insert(
    ['email' => 'john@example.com', 'votes' => 0]);
DB::table('users')->insert([
    ['email' => 'taylor@example.com', 'votes' => 0],
    ['email' => 'dayle@example.com, 'votes' => 0]
]);

Updates

DB::table('users')
            ->where('id', 1)
            ->update(['votes' => 1]);

Deletes

DB::table('users')->delete();

DB::table('users')->where('votes', '>', 100)->delete();

Pessimistic Locking

DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

Laravel Query Builder — Structure map

Clickable & Draggable!

Laravel Query Builder — Related pages: