Sign in Get started

Laravel Tutorial · Database and Eloquent · Lesson 14 of 27

The Query Builder

Query the database fluently with DB::table(): where clauses, ordering, aggregates, joins and grouping, with bindings that prevent SQL injection.

Create a free account to save this page, take notes and track your progress. Sign in

Fluent queries

DB::table('orders') starts a query; chain methods and finish with get(), first(), value(), pluck(), count(), sum(), exists(), paginate()… Values are always bound as parameters, so user input can't inject SQL.

Need Method
Filter where('status', 'paid'), where('total', '>', 100), orWhere, whereIn, whereNull, whereBetween, whereDate
Sort / limit orderBy('created_at', 'desc'), latest(), limit(10), offset(20)
Aggregates count(), sum('total'), avg(), max()
Joins / groups join('users', 'users.id', '=', 'orders.user_id'), groupBy, having, selectRaw
Write insert, update, increment, delete, upsert

Use toSql() (or toRawSql()) to see the SQL a builder will run.

Filtering, aggregates, grouping and updates
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

Schema::create('orders', function (Blueprint $t) {
    $t->id();
    $t->string('customer');
    $t->string('status');
    $t->integer('total_cents');
});
DB::table('orders')->insert([
    ['customer' => 'ana', 'status' => 'paid', 'total_cents' => 4500],
    ['customer' => 'ben', 'status' => 'pending', 'total_cents' => 1200],
    ['customer' => 'ana', 'status' => 'paid', 'total_cents' => 9900],
    ['customer' => 'cleo', 'status' => 'refunded', 'total_cents' => 3000],
    ['customer' => 'ben', 'status' => 'paid', 'total_cents' => 2500],
]);

$big = DB::table('orders')->where('status', 'paid')->where('total_cents', '>', 3000)->orderByDesc('total_cents')->pluck('customer');
echo 'big paid orders by: ', $big->implode(', '), "\n";
echo 'paid revenue: ', DB::table('orders')->where('status', 'paid')->sum('total_cents') / 100, "\n";
echo 'customers: ', DB::table('orders')->distinct()->orderBy('customer')->pluck('customer')->implode(', '), "\n";

$perCustomer = DB::table('orders')
    ->select('customer', DB::raw('count(*) as orders'), DB::raw('sum(total_cents) as cents'))
    ->whereIn('status', ['paid', 'pending'])
    ->groupBy('customer')
    ->having(DB::raw('sum(total_cents)'), '>', 2000)
    ->orderBy('customer')
    ->get();
foreach ($perCustomer as $row) {
    echo "$row->customer: $row->orders order(s), ", $row->cents / 100, "\n";
}

DB::table('orders')->where('status', 'pending')->update(['status' => 'paid']);
echo 'pending left: ', DB::table('orders')->where('status', 'pending')->count(), "\n";

$evil = "ana' OR '1'='1";
echo 'injection attempt matches: ', DB::table('orders')->where('customer', $evil)->count(), "\n";
echo DB::table('orders')->where('customer', 'ana')->orderBy('id')->toSql(), "\n";

Output

big paid orders by: ana, ana
paid revenue: 169
customers: ana, ben, cleo
ana: 2 order(s), 144
ben: 2 order(s), 37
pending left: 0
injection attempt matches: 0
select * from "orders" where "customer" = ? order by "id" asc

Run this on your computer — see “Get Started” for setup.

Test yourself

How does the query builder protect against SQL injection?

  1. It escapes HTML
  2. Values are sent as bound parameters, separate from the SQL text
  3. It blocks quotes
  4. It only allows numbers
Show answer

Answer: Values are sent as bound parameters, separate from the SQL text

Was this page helpful?