🔎 Querying with Eloquent

The Query Builder

Every Eloquent query flows through a query builder: you chain methods in PHP, and Eloquent compiles them into parameterized SQL. You describe what you want; the builder handles how to ask the database.

That abstraction buys you four things at once:

• Database-agnostic — the same code runs on MySQL, PostgreSQL, SQLite, and SQL Server.
• Injection-safe — values are bound as parameters, never concatenated into SQL strings.
• Readable — method chains read close to plain English.
• Composable — you can build a query in pieces and only execute it when you call get().

💡 Analogy: The builder is a translator. You speak elegant, object-oriented PHP; it speaks fluent, optimized SQL to the database on your behalf — and it never lets a malicious phrase slip through unescaped.

Retrieving Records

Start with the everyday retrieval methods. Note which return a single model versus a collection:

$users = User::all();                       // Collection of every row
$user  = User::find(1);                      // one model, or null
$users = User::find([1, 2, 3]);              // Collection by many IDs
$user  = User::where('active', true)->first(); // first match, or null

// The orFail variants throw ModelNotFoundException (auto 404 in a route)
$user  = User::findOrFail(1);
$user  = User::where('email', $email)->firstOrFail();

// Find or create in one call
$user  = User::firstOrCreate(
    ['email' => 'john@example.com'],        // search by this
    ['name'  => 'John', 'password' => bcrypt('secret')] // create with this
);

Methods returning multiple models give you an Illuminate\Database\Eloquent\Collection — a supercharged array with dozens of helpers:

$users = User::all();

$admins   = $users->where('is_admin', true);  // filter in memory
$names    = $users->pluck('name');            // Collection of names
$byType   = $users->groupBy('type');          // grouped Collection
$avgAge   = $users->avg('age');               // aggregate in memory

Where Clauses

The where family is where most filtering happens. The basics:

$users = User::where('active', true)->get();
$users = User::where('age', '>=', 21)->get();

// Chained wheres are AND; orWhere adds OR
$users = User::where('active', true)
    ->where('age', '>=', 21)
    ->orWhere('role', 'admin')
    ->get();

Specialized wheres

User::whereBetween('age', [18, 65])->get();
User::whereIn('id', [1, 2, 3])->get();
User::whereNotNull('email_verified_at')->get();
User::whereDate('created_at', '2026-01-01')->get();
User::whereColumn('created_at', '>', 'updated_at')->get();

// Query a JSON column (MySQL / PostgreSQL / SQLite)
User::where('preferences->theme', 'dark')->get();

Grouped clauses

Pass a closure to wrap conditions in parentheses — essential for mixing AND and OR correctly:

// WHERE (role = 'admin' AND active = 1)
//    OR (role = 'user'  AND verified = 1)
$users = User::where(function ($query) {
        $query->where('role', 'admin')->where('active', true);
    })
    ->orWhere(function ($query) {
        $query->where('role', 'user')->where('verified', true);
    })
    ->get();

Ordering & Pagination

Ordering & limiting

User::orderBy('name')->get();
User::orderBy('created_at', 'desc')->get();
User::latest()->get();               // shortcut for created_at desc
User::oldest()->get();               // created_at asc
User::inRandomOrder()->first();      // one random row

User::take(10)->get();               // LIMIT 10
User::skip(20)->take(10)->get();     // OFFSET 20 LIMIT 10

Pagination

For real UIs you rarely dump every row — you paginate. Eloquent offers three flavors:

// Controller
$users = User::where('active', true)
    ->orderBy('name')
    ->paginate(15)
    ->withQueryString();   // keep ?filter=... across page links

{{-- Blade view --}}
@foreach ($users as $user)
    <div>{{ $user->name }}</div>
@endforeach

{{ $users->links() }}   {{-- renders the pager --}}

💡 Why cursor pagination scales: paginate() uses OFFSET, which forces the database to scan and discard all skipped rows — page 10,000 is slow. cursorPaginate() instead remembers "the last id I saw" and uses a WHERE id > ?, so every page costs the same.

Aggregates

Aggregates ask the database for a summary instead of rows — far cheaper than pulling everything and counting in PHP:

$count   = User::count();
$active  = User::where('active', true)->count();
$revenue = Order::sum('total');
$avg     = Order::avg('total');
$max     = Order::max('total');

// Grouped aggregate: order count per status
$byStatus = Order::selectRaw('status, COUNT(*) as count')
    ->groupBy('status')
    ->get();

Relationship-aware aggregates let you sort or filter by related data without a manual join:

// Most-commented posts first
$posts = Post::withCount('comments')
    ->orderByDesc('comments_count')
    ->get();

// Attach the average review rating as reviews_avg_rating
$products = Product::withAvg('reviews', 'rating')->get();

Conditional Queries

Filters that depend on user input tempt you into a thicket of if statements. The when() method keeps the chain fluent — the closure runs only when its condition is truthy:

$users = User::query()
    ->when($request->filled('role'), function ($query) use ($request) {
        $query->where('role', $request->input('role'));
    })
    ->when($request->filled('search'), function ($query) use ($request) {
        $search = $request->input('search');
        $query->where(function ($q) use ($search) {
            $q->where('name', 'like', "%{$search}%")
              ->orWhere('email', 'like', "%{$search}%");
        });
    })
    ->paginate(15);

when() also takes a second closure as an "else" branch, and unless() is its inverse:

$users = User::when(
    $sortField,
    fn ($q) => $q->orderBy($sortField, $sortDirection ?? 'asc'),
    fn ($q) => $q->orderBy('name'),   // default when no sort chosen
)->get();

// Only limit visibility for non-admins
$users = User::unless($isAdmin, fn ($q) => $q->where('visible', true))->get();

Local & Global Scopes

Scopes package a common constraint so you write it once and reuse it everywhere.

Local scopes

Prefix a model method with scope and call it (minus the prefix) as a query method:

use Illuminate\Database\Eloquent\Builder;

class User extends Model
{
    public function scopeActive(Builder $query): Builder
    {
        return $query->where('active', true);
    }

    public function scopeOfType(Builder $query, string $type): Builder
    {
        return $query->where('type', $type);
    }
}

// Read almost like a sentence
$admins = User::active()->ofType('admin')->orderBy('name')->get();

Global scopes

A global scope applies to every query on the model automatically — perfect for soft deletes or multi-tenant isolation. In Laravel 11 the tidiest way is the #[ScopedBy] attribute:

<?php

namespace App\Models\Scopes;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;

class ActiveScope implements Scope
{
    public function apply(Builder $builder, Model $model): void
    {
        $builder->where('active', true);
    }
}

use App\Models\Scopes\ActiveScope;
use Illuminate\Database\Eloquent\Attributes\ScopedBy;

#[ScopedBy(ActiveScope::class)]
class User extends Model
{
    // Every query now includes WHERE active = 1
}

// Opt out when you genuinely need inactive users
$all = User::withoutGlobalScope(ActiveScope::class)->get();

Performance & Chunking

Chunking large datasets

Never load a million rows into memory at once. Process them in batches:

// Process 100 at a time
User::where('active', true)->chunkById(100, function ($users) {
    foreach ($users as $user) {
        // ...work on each user
    }
});

// Or lazily iterate one model at a time, memory-friendly
foreach (User::lazy() as $user) {
    // ...
}

chunkById is safer than plain chunk when you modify rows inside the loop — it paginates by primary key rather than OFFSET, so shifting rows don't get skipped.

Query-tuning checklist

// See exactly what SQL your code generates
DB::enableQueryLog();
$users = User::where('active', true)->get();
dump(DB::getQueryLog());   // array of every query + bindings

💡 Analogy: Query tuning is engine tuning — small, deliberate adjustments to how you fetch data compound into large gains once traffic grows. Measure first (query log, Laravel Telescope), then optimize the real bottleneck.

Hands-on Exercise

public function index(Request $request)
{
    $products = Product::query()
        ->where('active', true)
        ->when($request->filled('search'), function ($query) use ($request) {
            $term = $request->input('search');
            $query->where(function ($q) use ($term) {
                $q->where('name', 'like', "%{$term}%")
                  ->orWhere('description', 'like', "%{$term}%");
            });
        })
        ->when($request->filled('category'), fn ($q) =>
            $q->where('category_id', $request->input('category'))
        )
        ->when($request->boolean('on_sale'), fn ($q) =>
            $q->whereColumn('sale_price', '<', 'price')
        )
        ->with('category')
        ->withAvg('reviews', 'rating')
        ->orderBy('name')
        ->paginate(24)
        ->withQueryString();

    return view('products.index', compact('products'));
}

Summary & Quiz

📚 Further Reading

• Laravel 11 Docs — Retrieving Models
• Laravel 11 Docs — Query Builder
• Laravel 11 Docs — Pagination

🚀 What's Next?

You can now read and shape data with confidence. Next, in Form Handling in Laravel, you'll capture user input, validate it, and turn it into the models you've just learned to query.