Master Laravel’s Query Builder: Retrieve, Filter, and Manipulate Data Efficiently

Introduction

Laravel’s database query builder provides a fluent interface for creating and running SQL queries across all supported database systems. It uses PDO parameter binding, which automatically protects applications from SQL injection.

Retrieving Results

Get All Rows

$users = DB::table('users')->get();
foreach ($users as $user) {
    echo $user->name;
}

The get method returns an Illuminate\Support\Collection of StdClass objects, allowing property access for each column.

Single Row or Column

$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;

$email = DB::table('users')->where('name', 'John')->value('email');

first

returns the first matching record as a StdClass object, while value returns a single column value directly.

Pluck Column Values

$titles = DB::table('roles')->pluck('title');
foreach ($titles as $title) {
    echo $title;
}

// Custom keys
$roles = DB::table('roles')->pluck('title', 'name');
foreach ($roles as $name => $title) {
    echo $title;
}

Chunking Large Result Sets

DB::table('users')
    ->orderBy('id')
    ->chunk(100, function ($users) {
        foreach ($users as $user) {
            // Process each user
        }
    });

Returning false from the closure stops further chunks.

Aggregates

$count = DB::table('users')->count();
$maxPrice = DB::table('orders')->max('price');
$average = DB::table('orders')->where('finalized', 1)->avg('price');

All aggregate methods ( count, max, min, avg, sum) can be chained with other query constraints.

Existence Checks

$exists = DB::table('orders')
    ->where('finalized', 1)
    ->exists();

$doesntExist = DB::table('orders')
    ->where('finalized', 1)
    ->doesntExist();

Select Statements

Specific Columns

$users = DB::table('users')
    ->select('name', 'email as user_email')
    ->get();

Distinct

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

Adding Columns to an Existing Query

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

Raw Expressions

Using DB::raw

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

Raw expressions are injected directly into the SQL, so they must be used carefully to avoid injection.

selectRaw

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

whereRaw / orWhereRaw

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

havingRaw

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

orderByRaw

$orders = DB::table('orders')
    ->orderByRaw('updated_at - created_at DESC')
    ->get();

Joins

Inner Join

$users = DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.price')
    ->get();

Left Join

$users = DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();

Cross Join

$result = DB::table('sizes')
    ->crossJoin('colours')
    ->get();

Advanced Join with Closure

DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->orOn(/* additional conditions */);
    })
    ->get();

Join with Where Conditions

DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->where('contacts.user_id', '>', 5);
    })
    ->get();

Unions

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

Use unionAll for a union that does not remove duplicate rows.

Where Clauses

Basic Where

$users = DB::table('users')->where('votes', '=', 100)->get();
$users = DB::table('users')->where('votes', 100)->get();
$users = DB::table('users')->where('votes', '>=', 100)->get();
$users = DB::table('users')->where('name', 'like', 'T%')->get();

Array Where

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1']
])->get();

Or Clause

$users = DB::table('users')
    ->where('votes', '>', 100)
    ->orWhere('name', 'John')
    ->get();

Range and Set Clauses

$users = DB::table('users')->whereBetween('votes', [1, 100])->get();
$users = DB::table('users')->whereNotBetween('votes', [1, 100])->get();
$users = DB::table('users')->whereIn('id', [1, 2, 3])->get();
$users = DB::table('users')->whereNotIn('id', [1, 2, 3])->get();
$users = DB::table('users')->whereNull('updated_at')->get();
$users = DB::table('users')->whereNotNull('updated_at')->get();

Date/Time Where

$users = DB::table('users')->whereDate('created_at', '2016-12-31')->get();
$users = DB::table('users')->whereMonth('created_at', '12')->get();
$users = DB::table('users')->whereDay('created_at', '31')->get();
$users = DB::table('users')->whereYear('created_at', '2016')->get();
$users = DB::table('users')->whereTime('created_at', '=', '11:20')->get();

Column Comparison

$users = DB::table('users')->whereColumn('first_name', 'last_name')->get();
$users = DB::table('users')->whereColumn('updated_at', '>', 'created_at')->get();
$users = DB::table('users')->whereColumn([
    ['first_name', '=', 'last_name'],
    ['updated_at', '>', 'created_at']
])->get();

Nested Where Groups

$users = DB::table('users')
    ->where('name', '=', 'John')
    ->orWhere(function ($query) {
        $query->where('votes', '>', 100)
              ->where('title', '<>', 'Admin');
    })
    ->get();

whereExists

$users = DB::table('users')
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
              ->from('orders')
              ->whereRaw('orders.user_id = users.id');
    })
    ->get();

JSON Where (MySQL 5.7+ / PostgreSQL)

$users = DB::table('users')
    ->where('options->language', 'en')
    ->get();

$users = DB::table('users')
    ->where('preferences->dining->meal', 'salad')
    ->get();

Ordering, Grouping, Limits

orderBy

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

latest / oldest

$latestUser = DB::table('users')->latest()->first();
$oldestUser = DB::table('users')->oldest()->first();

Random Order

$randomUser = DB::table('users')->inRandomOrder()->first();

groupBy / having

$users = DB::table('users')
    ->groupBy('account_id')
    ->having('account_id', '>', 100)
    ->get();

skip / take (or offset / limit)

$users = DB::table('users')
    ->skip(10)
    ->take(5)
    ->get();

$users = DB::table('users')
    ->offset(10)
    ->limit(5)
    ->get();

Conditional Queries

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

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

Inserts

// Single record
DB::table('users')->insert([
    'email' => '[email protected]',
    'votes' => 0
]);

// Multiple records
DB::table('users')->insert([
    ['email' => '[email protected]', 'votes' => 0],
    ['email' => '[email protected]', 'votes' => 0]
]);

// Insert and get auto‑increment ID
$id = DB::table('users')->insertGetId([
    'email' => '[email protected]',
    'votes' => 0
]);

Updates

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

// Update JSON field (MySQL/PostgreSQL)
DB::table('users')
    ->where('id', 1)
    ->update(['options->enabled' => true]);

// Increment / decrement
DB::table('users')->increment('votes'); // +1
DB::table('users')->increment('votes', 5); // +5
DB::table('users')->decrement('votes'); // -1
DB::table('users')->decrement('votes', 5); // -5

// Increment with additional column updates
DB::table('users')
    ->where('id', 1)
    ->increment('votes', 1, ['name' => 'John']);

Deletes

// Delete rows matching a condition
DB::table('users')
    ->where('votes', '>', 100)
    ->delete();

// Truncate table (reset auto‑increment)
DB::table('users')->truncate();

Pessimistic Locking

// Shared lock (prevents other transactions from modifying rows)
$users = DB::table('users')
    ->where('votes', '>', 100)
    ->sharedLock()
    ->get();

// Exclusive lock for update
$users = DB::table('users')
    ->where('votes', '>', 100)
    ->lockForUpdate()
    ->get();

The query builder’s fluent methods make complex SQL operations readable and maintainable while keeping the underlying queries secure and efficient.