Querying with Eloquent

Introduction to Eloquent Query Builder

Eloquent's query builder provides a fluent, intuitive interface for creating and executing database queries. It serves as an abstraction layer over raw SQL, allowing you to write database queries using method chaining rather than writing SQL strings.

Think of the query builder as a sophisticated translator - you communicate in an elegant, object-oriented language, and it translates your instructions into optimized SQL that the database understands.

graph LR A[Eloquent Code] --> B[Query Builder] B --> C[SQL Query] C --> D[Database] D --> E[Query Results] E --> F[Eloquent Collection/Model]

This abstraction offers several advantages:

Database Agnostic: Write queries that work across different database systems
SQL Injection Protection: Automatic parameter binding prevents SQL injection attacks
Readable Code: Fluent interface results in more readable and maintainable code
Complex Query Building: Easily construct sophisticated queries through method chaining

In this lecture, we'll explore Eloquent's powerful querying capabilities beyond the basic CRUD operations we discussed earlier.

Retrieving Results

Basic Retrieval Methods


// Get all records
$users = User::all();

// Get by primary key
$user = User::find(1);
$users = User::find([1, 2, 3]); // Multiple IDs

// Get first record matching criteria
$user = User::where('active', true)->first();

// Get with a fallback for not found
$user = User::findOrFail(1); // Throws ModelNotFoundException if not found
$user = User::firstOrFail(['email' => 'test@example.com']); // Throws exception if not found

// Create or retrieve based on certain attributes
$user = User::firstOrCreate(
    ['email' => 'john@example.com'], // Attributes to search by
    ['name' => 'John', 'password' => bcrypt('password')] // Additional attributes if creating
);

// Update existing or create new based on attributes
$user = User::updateOrCreate(
    ['email' => 'john@example.com'], // Attributes to search by
    ['name' => 'John Doe', 'active' => true] // Attributes to update or create with
);

Collections

Eloquent retrieval methods that return multiple models will return instances of the Illuminate\Database\Eloquent\Collection class, which extends Laravel's base collection and provides additional functionality:


// Collection methods
$users = User::all();

// Filter users who are admins
$admins = $users->filter(function ($user) {
    return $user->is_admin;
});

// Transform collection
$userNames = $users->map(function ($user) {
    return $user->name;
});

// Find in collection
$john = $users->firstWhere('name', 'John');

// Pluck specific fields
$emails = $users->pluck('email'); // Collection of emails
$emailsById = $users->pluck('email', 'id'); // Collection of emails keyed by ID

// Group by field
$usersByType = $users->groupBy('type');

// Collection statistics
$average = $users->avg('age');
$sum = $users->sum('purchases');
$min = $users->min('age');
$max = $users->max('age');

Collections are like smart arrays with dozens of helper methods that make data manipulation much easier. Think of them as specialized toolkits for working with groups of models.

Building Queries

Where Clauses


// Basic where clauses
$users = User::where('active', true)->get();
$users = User::where('age', '>=', 21)->get();
$users = User::where('role', 'admin')->orWhere('role', 'editor')->get();

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

// Where with OR condition
$users = User::where('role', 'admin')
    ->orWhere('role', 'editor')
    ->get();

// Where with array of conditions
$users = User::where([
    ['active', '=', true],
    ['age', '>=', 21]
])->get();

// Where with JSON field (MySQL/PostgreSQL)
$users = User::where('preferences->theme', 'dark')->get();

Advanced Where Clauses


// Where between
$users = User::whereBetween('age', [18, 65])->get();
$users = User::whereNotBetween('age', [0, 17])->get();

// Where in
$users = User::whereIn('id', [1, 2, 3])->get();
$users = User::whereNotIn('id', [1, 2, 3])->get();

// Where null/not null
$users = User::whereNull('deleted_at')->get();
$users = User::whereNotNull('email_verified_at')->get();

// Where date
$posts = Post::whereDate('created_at', '2023-01-01')->get();
$posts = Post::whereMonth('created_at', '1')->get(); // January
$posts = Post::whereDay('created_at', '1')->get(); // 1st day of month
$posts = Post::whereYear('created_at', '2023')->get();
$posts = Post::whereTime('created_at', '=', '14:30:00')->get();

// Where column (compare two columns)
$users = User::whereColumn('first_name', 'last_name')->get();
$users = User::whereColumn('created_at', '>', 'updated_at')->get();

Grouped Where Clauses


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

These query building methods allow you to create highly specific queries using readable PHP code. It's like having a specialized language for describing exactly which records you want to retrieve.

Ordering, Grouping, and Limiting

Ordering Results


// Basic ordering
$users = User::orderBy('name', 'asc')->get();
$users = User::orderBy('created_at', 'desc')->get();

// Multiple order columns
$users = User::orderBy('role')
    ->orderBy('name')
    ->get();

// Latest (shortcut for orderBy created_at desc)
$recentUsers = User::latest()->get();

// Oldest (shortcut for orderBy created_at asc)
$oldestUsers = User::oldest()->get();

// Random order
$randomUsers = User::inRandomOrder()->get();

// Order by relationship count
$popularPosts = Post::withCount('comments')
    ->orderBy('comments_count', 'desc')
    ->get();

// Order by relationship field
$users = User::whereHas('posts', function ($query) {
    $query->select('user_id')
        ->groupBy('user_id')
        ->orderByRaw('COUNT(*) DESC');
})
->get();

Grouping and Having


// Group by with having clause
$userGroups = User::select('role', DB::raw('COUNT(*) as count'))
    ->groupBy('role')
    ->having('count', '>', 10)
    ->get();

// Using havingRaw for more complex conditions
$activeUserGroups = User::select('role', DB::raw('COUNT(*) as count'))
    ->where('active', true)
    ->groupBy('role')
    ->havingRaw('COUNT(*) > ?', [5])
    ->get();

Limiting Results


// Take/limit
$users = User::take(10)->get(); // LIMIT 10
$users = User::limit(10)->get(); // Same as take

// Skip/offset
$users = User::skip(10)->take(5)->get(); // OFFSET 10 LIMIT 5
$users = User::offset(10)->limit(5)->get(); // Same as skip/take

// Pagination
$users = User::paginate(15); // 15 per page
$users = User::where('active', true)->paginate(15);

// Simple pagination (faster, but without total count)
$users = User::simplePaginate(15);

// Custom pagination
$users = User::where('active', true)
    ->orderBy('name')
    ->paginate(
        perPage: 10,
        columns: ['id', 'name', 'email'],
        pageName: 'users',
        page: request()->input('users', 1)
    );

These methods help you fine-tune how results are ordered, grouped, and limited, similar to how a librarian might organize books by certain criteria and present them in manageable batches.

Selecting Specific Columns


// Select specific columns
$users = User::select('id', 'name', 'email')->get();

// Select with alias
$users = User::select('name', 'email as user_email')->get();

// Add a select to an existing query
$query = User::query();
if ($includeRole) {
    $query->addSelect('role');
}
$users = $query->get();

// Select distinct values
$roles = User::select('role')->distinct()->get();

// Select with raw expressions
$users = User::selectRaw('COUNT(*) as user_count, role')
    ->groupBy('role')
    ->get();

// Selecting with subqueries
$latestPostsPerUser = User::select(['users.*', 
    Post::select('title')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->limit(1)
        ->as('latest_post_title')
])->get();

Selecting specific columns is like choosing exactly which fields to display in a form - you get only the data you need, which can significantly improve performance for large datasets.

Raw Expressions

Sometimes you need to use database-specific SQL functions or expressions that aren't directly available through the query builder's methods:


// Using selectRaw
$users = User::selectRaw('COUNT(*) as user_count, active')
    ->groupBy('active')
    ->get();

// Using whereRaw
$users = User::whereRaw('age > IF(role = "admin", 25, 18)')
    ->get();

// Using orderByRaw
$users = User::orderByRaw('FIELD(role, "admin", "editor", "user")')
    ->get();

// Using groupByRaw
$users = User::select('role')
    ->groupByRaw('role WITH ROLLUP')
    ->get();

// Using havingRaw
$userGroups = User::select('role', DB::raw('COUNT(*) as count'))
    ->groupBy('role')
    ->havingRaw('COUNT(*) > ?', [5])
    ->get();

// DB::raw for expressions in select
$users = User::select([
    'name',
    DB::raw('YEAR(created_at) as registration_year'),
    DB::raw('CONCAT(first_name, " ", last_name) as full_name')
])->get();

Raw expressions are like having direct access to the database's native language when needed. They provide an escape hatch for when the abstraction doesn't quite cover what you need to do.

graph TD A[Eloquent Query Builder] -->|selectRaw, whereRaw, etc.| B[Raw SQL Expressions] A -->|select, where, orderBy, etc.| C[Abstracted Methods] B --> D[SQL Query] C --> D D --> E[Database]

However, use raw expressions judiciously, as they can make your code database-specific and potentially vulnerable to SQL injection if you're not careful with binding parameters.

Conditional Queries

Often, you need to build queries conditionally based on user input or application state:

Traditional Approach


$query = User::query();

if ($request->has('role')) {
    $query->where('role', $request->input('role'));
}

if ($request->has('active')) {
    $query->where('active', $request->boolean('active'));
}

if ($request->has('search')) {
    $search = $request->input('search');
    $query->where(function ($q) use ($search) {
        $q->where('name', 'like', "%{$search}%")
          ->orWhere('email', 'like', "%{$search}%");
    });
}

$users = $query->get();

Using the when Method


$users = User::when($request->has('role'), function ($query) use ($request) {
        return $query->where('role', $request->input('role'));
    })
    ->when($request->has('active'), function ($query) use ($request) {
        return $query->where('active', $request->boolean('active'));
    })
    ->when($request->has('search'), function ($query) use ($request) {
        $search = $request->input('search');
        return $query->where(function ($q) use ($search) {
            $q->where('name', 'like', "%{$search}%")
              ->orWhere('email', 'like', "%{$search}%");
        });
    })
    ->get();

Conditional Methods with Default


// If $sortField is null, sort by name
$users = User::when($sortField, 
    function ($query, $sortField) use ($sortDirection) {
        return $query->orderBy($sortField, $sortDirection ?? 'asc');
    }, 
    function ($query) {
        return $query->orderBy('name', 'asc');
    }
)->get();

Using unless Method (Inverse of when)


$users = User::unless($isAdmin, function ($query) {
    return $query->where('visible', true);
})->get();

Conditional queries are like having adaptive filtering - they adjust based on the specific criteria provided, making your code more flexible and reusable.

Chunking Results

When working with large datasets, you may need to process records in smaller chunks to avoid memory issues:


// Basic chunking
User::chunk(100, function ($users) {
    foreach ($users as $user) {
        // Process each user
    }
});

// Chunking with conditions
User::where('active', true)
    ->chunk(100, function ($users) {
        foreach ($users as $user) {
            // Process each user
        }
    });

// Stopping the chunking
User::chunk(100, function ($users) {
    foreach ($users as $user) {
        // Process each user
        
        if (/* some condition */) {
            return false; // Stop chunking
        }
    }
});

// Chunking by ID (more efficient for large datasets)
User::chunkById(100, function ($users) {
    foreach ($users as $user) {
        // Process each user
    }
});

// Using lazy() for memory-efficient iteration
foreach (User::lazy() as $user) {
    // Process each user one at a time without loading all into memory
}

Chunking is like processing a large stack of papers in manageable batches rather than trying to handle the entire stack at once. It helps manage memory usage and keeps your application responsive.

Aggregates

Eloquent provides convenient methods for common aggregate operations:


// Count
$userCount = User::count();
$activeUserCount = User::where('active', true)->count();

// Sum
$totalRevenue = Order::sum('total');
$monthlyRevenue = Order::whereMonth('created_at', now()->month)->sum('total');

// Average
$averageAge = User::avg('age');
$averageOrderValue = Order::avg('total');

// Min / Max
$youngestAge = User::min('age');
$oldestAge = User::max('age');
$largestOrder = Order::max('total');

// Aggregate with grouping
$orderCountsByStatus = Order::select('status', DB::raw('COUNT(*) as count'))
    ->groupBy('status')
    ->get();

// Multiple aggregates in one query
$userStats = User::selectRaw('
    COUNT(*) as count,
    AVG(age) as average_age,
    MIN(age) as min_age,
    MAX(age) as max_age,
    SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) as active_count
')->first();

Aggregates are like getting summary statistics from your data - instead of examining each record individually, you get high-level insights about the entire dataset or specific groups within it.

Subqueries

Eloquent allows you to incorporate subqueries into your main queries for more complex operations:

Subqueries in Select


$users = User::select([
    'users.*',
    // Get latest post title for each user
    Post::select('title')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->limit(1)
        ->as('latest_post_title'),
    // Count posts for each user
    Post::selectRaw('COUNT(*)')
        ->whereColumn('user_id', 'users.id')
        ->as('post_count')
])->get();

Subqueries in Where


// Users who have published posts in the last week
$users = User::whereExists(function ($query) {
    $query->select(DB::raw(1))
        ->from('posts')
        ->whereColumn('user_id', 'users.id')
        ->where('published', true)
        ->where('created_at', '>=', now()->subWeek());
})->get();

// Alternative using whereHas with relationship
$users = User::whereHas('posts', function ($query) {
    $query->where('published', true)
          ->where('created_at', '>=', now()->subWeek());
})->get();

Ordering by Subquery


// Order users by their post count
$users = User::orderByDesc(
    Post::selectRaw('COUNT(*)')
        ->whereColumn('user_id', 'users.id')
)->get();

Subqueries are like nested questions in a database conversation - they allow you to incorporate the results of one query into another, enabling more sophisticated data retrieval patterns.

Advanced Relationship Queries

Eager Loading with Constraints


// Load only published posts
$users = User::with(['posts' => function ($query) {
    $query->where('published', true);
}])->get();

// Load only the 3 most recent comments per post
$posts = Post::with(['comments' => function ($query) {
    $query->latest()->limit(3);
}])->get();

// Nested eager loading with constraints
$users = User::with(['posts' => function ($query) {
    $query->where('published', true);
}, 'posts.comments' => function ($query) {
    $query->where('approved', true)->latest();
}])->get();

Eager Loading Specific Columns


// Select specific columns from the relationship
$posts = Post::with(['user:id,name,email'])->get();

// Multiple relationships with specific columns
$posts = Post::with([
    'user:id,name,email',
    'comments:id,post_id,user_id,content'
])->get();

// Nested relationships with specific columns
$users = User::with([
    'posts:id,user_id,title,created_at',
    'posts.comments:id,post_id,content'
])->get();

Counting Related Models


// Add counts of related models
$posts = Post::withCount(['comments', 'likes'])->get();

// Access the counts
foreach ($posts as $post) {
    echo $post->comments_count; // Number of comments
    echo $post->likes_count; // Number of likes
}

// Counts with constraints
$posts = Post::withCount([
    'comments',
    'approvedComments' => function ($query) {
        $query->where('approved', true);
    }
])->get();

// Conditional loading
$loadComments = true;
$posts = Post::when($loadComments, function ($query) {
    return $query->with('comments');
})->get();

Has / Doesnt Have / WhereHas


// Posts that have comments
$posts = Post::has('comments')->get();

// Posts with at least 5 comments
$posts = Post::has('comments', '>=', 5)->get();

// Posts that have approved comments
$posts = Post::whereHas('comments', function ($query) {
    $query->where('approved', true);
})->get();

// Posts that don't have any approved comments
$posts = Post::whereDoesntHave('comments', function ($query) {
    $query->where('approved', true);
})->get();

// Complex nested relationship queries
$users = User::whereHas('posts.comments', function ($query) {
    $query->where('content', 'like', '%great%');
})->get(); // Users who have posts with comments containing "great"

These advanced relationship queries allow you to precisely specify which related data you want to retrieve and how it should be filtered. It's like having a sophisticated filtering system that understands the connections between different entities in your data model.

Querying JSON Data

Many modern databases support JSON columns (MySQL 5.7+, PostgreSQL, SQLite 3.9+). Eloquent provides convenient methods for querying JSON data:


// Migrations
Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->json('preferences')->nullable();
    $table->json('metadata')->nullable();
    $table->timestamps();
});

// Setting JSON data
$user = User::create([
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'preferences' => [
        'theme' => 'dark',
        'notifications' => [
            'email' => true,
            'push' => false
        ]
    ]
]);

// Querying JSON data
$darkThemeUsers = User::where('preferences->theme', 'dark')->get();

// Nested JSON paths
$emailNotificationUsers = User::where('preferences->notifications->email', true)->get();

// JSON contains operator (PostgreSQL)
$users = User::whereJsonContains('preferences->tags', ['developer'])->get();

// JSON length operator
$users = User::whereJsonLength('preferences->tags', '>', 2)->get();

Casting JSON attributes in your model makes them even easier to work with:


class User extends Model
{
    protected $casts = [
        'preferences' => 'array',  // or 'json', 'collection', 'object'
        'metadata' => 'array'
    ];
}

// Now you can work with JSON data as PHP arrays/objects
$user = User::find(1);
$theme = $user->preferences['theme'];

$user->preferences['notifications']['push'] = true;
$user->save();

JSON querying capabilities are like having a flexible schema within a traditional database - you get the structure and performance of SQL with some of the flexibility of NoSQL databases.

Pagination

Pagination is a crucial feature for displaying large sets of data in manageable chunks:

Basic Pagination


// Controller method
public function index()
{
    $users = User::paginate(15); // 15 items per page
    
    return view('users.index', compact('users'));
}

// In the Blade template
<div class="users">
    @foreach ($users as $user)
        <div class="user">{{ $user->name }}</div>
    @endforeach
</div>

<div class="pagination">
    {{ $users->links() }}
</div>

Customizing Pagination


// Custom pagination
$users = User::paginate(
    perPage: 10,          // Items per page
    columns: ['id', 'name', 'email'], // Columns to select
    pageName: 'users_page', // Query parameter name
    page: request()->input('users_page', 1) // Current page
);

// Appending query parameters to pagination links
$users = User::paginate(15);
$users->appends(['sort' => 'name', 'filter' => 'active']);

// Or automatically append all current query parameters
$users = User::paginate(15);
$users->withQueryString();

// Customizing pagination view
$users = User::paginate(15);
// In a Blade template
{{ $users->links('vendor.pagination.bootstrap-4') }}

Simple Pagination

When you only need "Previous" and "Next" links without the overhead of counting all results:


$users = User::simplePaginate(15);

Cursor Pagination

For very large datasets, cursor pagination can be more efficient:


$users = User::orderBy('id')->cursorPaginate(15);

Pagination is like dividing a book into pages - it makes large amounts of data more manageable and improves both user experience and application performance.

Global Scopes

Global scopes allow you to add constraints to all queries for a given model:

Anonymous Global Scopes


class User extends Model
{
    /**
     * The "booted" method of the model.
     *
     * @return void
     */
    protected static function booted()
    {
        static::addGlobalScope('active', function (Builder $builder) {
            $builder->where('active', true);
        });
    }
}

// All queries will now include WHERE active = true
$users = User::all(); // Only active users

// Skip the global scope
$allUsers = User::withoutGlobalScope('active')->get();
$allUsers = User::withoutGlobalScopes()->get(); // Remove all global scopes

Class-Based Global Scopes


// Define a scope class
namespace App\Scopes;

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

class ActiveScope implements Scope
{
    /**
     * Apply the scope to a given Eloquent query builder.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $builder
     * @param  \Illuminate\Database\Eloquent\Model  $model
     * @return void
     */
    public function apply(Builder $builder, Model $model)
    {
        $builder->where('active', true);
    }
}

// Apply the scope in the model
use App\Scopes\ActiveScope;

class User extends Model
{
    protected static function booted()
    {
        static::addGlobalScope(new ActiveScope);
    }
}

Global scopes are like having automatic filters that apply to every query. They're useful for implementing model-level policies like soft deletes, tenant isolation, or active record filtering.

Local Scopes

Local scopes allow you to define commonly used query constraints that you can reuse throughout your application:


class User extends Model
{
    /**
     * Scope a query to only include active users.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeActive($query)
    {
        return $query->where('active', true);
    }
    
    /**
     * Scope a query to only include users of a given type.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @param  string  $type
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeOfType($query, $type)
    {
        return $query->where('type', $type);
    }
    
    /**
     * Scope a query to only include users created within a date range.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @param  string  $startDate
     * @param  string  $endDate
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeCreatedBetween($query, $startDate, $endDate)
    {
        return $query->whereBetween('created_at', [$startDate, $endDate]);
    }
}

// Usage
$activeUsers = User::active()->get();
$activeAdmins = User::active()->ofType('admin')->get();
$recentUsers = User::createdBetween(
    now()->subDays(30),
    now()
)->get();

// Chain with other query builder methods
$users = User::active()
    ->ofType('member')
    ->orderBy('name')
    ->paginate(15);

Local scopes are like having a library of pre-defined query templates that make your code more concise and readable. They encapsulate common filtering logic in a reusable format.

Advanced Query Techniques

Using Joins


// Inner join
$users = User::join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'orders.total')
    ->get();

// Left join
$users = User::leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'orders.total')
    ->get();

// Multiple joins
$users = User::join('orders', 'users.id', '=', 'orders.user_id')
    ->join('payments', 'orders.id', '=', 'payments.order_id')
    ->select('users.*', 'orders.total', 'payments.status')
    ->get();

// Join with conditions
$users = User::join('orders', function ($join) {
    $join->on('users.id', '=', 'orders.user_id')
         ->where('orders.status', '=', 'completed');
})->get();

While joins work well for many scenarios, Eloquent relationships often provide a more elegant approach for related data.

Union Queries


// Union of two queries
$first = User::where('active', true);
$users = User::where('vip', true)
    ->union($first)
    ->get();

// Union all (keeps duplicates)
$first = User::where('active', true);
$users = User::where('vip', true)
    ->unionAll($first)
    ->get();

Locking Queries (for transactions)


// Shared lock (FOR SHARE)
$users = User::where('active', true)
    ->sharedLock()
    ->get();

// Update lock (FOR UPDATE)
$users = User::where('active', true)
    ->lockForUpdate()
    ->get();

// Within a transaction
DB::transaction(function () {
    $user = User::where('id', 1)
        ->lockForUpdate()
        ->first();
    
    // Update the user...
    $user->save();
});

These advanced techniques provide solutions for more specialized querying needs, like combining results from different queries or ensuring data consistency in concurrent environments.

Query Performance Optimization

Selecting Only Needed Columns


// Instead of:
$users = User::all();

// Be specific about what you need:
$users = User::select('id', 'name', 'email')->get();

Eager Loading to Avoid N+1 Problems


// Instead of (causes N+1 queries):
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->user->name;
}

// Use eager loading:
$posts = Post::with('user')->get();
foreach ($posts as $post) {
    echo $post->user->name;
}

Use Chunk Processing for Large Datasets


// Instead of loading everything at once:
User::where('active', true)->chunk(100, function ($users) {
    foreach ($users as $user) {
        // Process each user
    }
});

Use Database Indexes


// In migration
Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('email')->unique();
    $table->string('name');
    $table->boolean('active');
    $table->string('type');
    $table->timestamps();
    
    // Add indexes to frequently queried columns
    $table->index('active');
    $table->index('type');
    $table->index(['active', 'type']); // Composite index
});

Use Query Caching for Expensive Queries


// Cache an expensive query for 60 minutes
$users = Cache::remember('vip_users', 60 * 60, function () {
    return User::where('vip', true)
        ->with(['orders', 'profile'])
        ->get();
});

Use Database Tools to Analyze Queries


// Enable query logging
DB::enableQueryLog();

// Run your query
$users = User::where('active', true)->get();

// See all executed queries
$queries = DB::getQueryLog();

There are also Laravel packages like Laravel Debugbar or Telescope that provide detailed query analysis.

Query optimization is like fine-tuning an engine - small adjustments in how you retrieve and process data can lead to significant performance improvements in your application.

Real-World Example: Advanced Product Search

Let's build a comprehensive product search function for an e-commerce application:


class ProductController extends Controller
{
    /**
     * Advanced product search and filtering
     */
    public function search(Request $request)
    {
        // Start building the query
        $query = Product::query();
        
        // Always get active products only
        $query->where('active', true);
        
        // Select specific fields
        $query->select([
            'products.id', 
            'products.name', 
            'products.slug', 
            'products.price', 
            'products.sale_price',
            'products.stock_quantity',
            'products.category_id',
            'products.brand_id'
        ]);
        
        // Add search term filtering
        $query->when($request->filled('search'), function ($query) use ($request) {
            $search = $request->input('search');
            return $query->where(function ($query) use ($search) {
                $query->where('products.name', 'like', "%{$search}%")
                    ->orWhere('products.description', 'like', "%{$search}%")
                    ->orWhere('products.sku', 'like', "%{$search}%");
            });
        });
        
        // Filter by category (including subcategories)
        $query->when($request->filled('category'), function ($query) use ($request) {
            $categoryId = $request->input('category');
            $category = Category::find($categoryId);
            
            if ($category) {
                // Get all descendant category IDs
                $categoryIds = Category::where('id', $categoryId)
                    ->orWhere('parent_id', $categoryId)
                    ->orWhereIn('parent_id', function ($query) use ($categoryId) {
                        $query->select('id')
                            ->from('categories')
                            ->where('parent_id', $categoryId);
                    })
                    ->pluck('id')
                    ->toArray();
                
                return $query->whereIn('products.category_id', $categoryIds);
            }
            
            return $query;
        });
        
        // Filter by brand
        $query->when($request->filled('brand'), function ($query) use ($request) {
            return $query->where('products.brand_id', $request->input('brand'));
        });
        
        // Filter by price range
        $query->when($request->filled(['price_min', 'price_max']), function ($query) use ($request) {
            $min = $request->input('price_min');
            $max = $request->input('price_max');
            
            return $query->where(function ($query) use ($min, $max) {
                $query->whereBetween('products.price', [$min, $max])
                    ->orWhereBetween('products.sale_price', [$min, $max]);
            });
        });
        
        // Filter by availability
        $query->when($request->filled('in_stock'), function ($query) use ($request) {
            if ($request->boolean('in_stock')) {
                return $query->where('products.stock_quantity', '>', 0);
            }
        });
        
        // Filter by tags
        $query->when($request->filled('tags'), function ($query) use ($request) {
            $tags = explode(',', $request->input('tags'));
            
            return $query->whereHas('tags', function ($query) use ($tags) {
                $query->whereIn('tags.slug', $tags);
            }, '=', count($tags)); // Must have ALL the specified tags
        });
        
        // Show only products on sale
        $query->when($request->boolean('on_sale'), function ($query) {
            return $query->where('products.sale_price', '>', 0)
                ->whereColumn('products.sale_price', '<', 'products.price');
        });
        
        // Add rating filter
        $query->when($request->filled('min_rating'), function ($query) use ($request) {
            $minRating = $request->input('min_rating');
            
            return $query->whereHas('reviews', function ($query) use ($minRating) {
                $query->selectRaw('AVG(rating) as avg_rating')
                    ->havingRaw('AVG(rating) >= ?', [$minRating])
                    ->groupBy('product_id');
            });
        });
        
        // Search by attributes (e.g., color, size)
        $query->when($request->filled('attributes'), function ($query) use ($request) {
            $attributes = json_decode($request->input('attributes'), true);
            
            foreach ($attributes as $key => $value) {
                $query->whereHas('attributes', function ($query) use ($key, $value) {
                    $query->where('name', $key)
                        ->where('value', $value);
                });
            }
            
            return $query;
        });
        
        // Sort results
        $query->when($request->filled('sort'), function ($query) use ($request) {
            $sortField = $request->input('sort');
            $direction = $request->input('direction', 'asc');
            
            switch ($sortField) {
                case 'price':
                    $query->orderByRaw('COALESCE(products.sale_price, products.price) ' . $direction);
                    break;
                    
                case 'popularity':
                    $query->withCount('orders')
                        ->orderBy('orders_count', $direction);
                    break;
                    
                case 'rating':
                    $query->withAvg('reviews', 'rating')
                        ->orderBy('reviews_avg_rating', $direction);
                    break;
                    
                case 'newest':
                    $query->orderBy('products.created_at', $direction);
                    break;
                    
                default:
                    $query->orderBy('products.name', $direction);
            }
            
            return $query;
        }, function ($query) {
            // Default sorting if none specified
            return $query->orderBy('products.name', 'asc');
        });
        
        // Eager load commonly needed relationships
        $query->with(['category', 'brand', 'mainImage', 'tags']);
        
        // Add average rating
        $query->withAvg('reviews', 'rating');
        
        // Paginate the results
        $perPage = $request->input('per_page', 24);
        $products = $query->paginate($perPage)->withQueryString();
        
        // Return view or JSON response
        if ($request->expectsJson()) {
            return response()->json([
                'data' => $products->items(),
                'pagination' => [
                    'total' => $products->total(),
                    'per_page' => $products->perPage(),
                    'current_page' => $products->currentPage(),
                    'last_page' => $products->lastPage()
                ]
            ]);
        }
        
        // Get filter options for sidebar
        $categories = Category::where('parent_id', null)->with('children')->get();
        $brands = Brand::orderBy('name')->get();
        $priceRange = [
            'min' => Product::min('price'),
            'max' => Product::max('price')
        ];
        $popularTags = Tag::withCount('products')
            ->orderBy('products_count', 'desc')
            ->take(10)
            ->get();
        
        return view('products.index', compact(
            'products',
            'categories',
            'brands',
            'priceRange',
            'popularTags'
        ));
    }
}

This comprehensive example demonstrates many of the query techniques we've covered:

Conditional query building with when()
Relationship querying with whereHas()
Subqueries for complex filtering scenarios
Aggregate functions with withAvg() and withCount()
Advanced sorting options
Eager loading relationships to avoid N+1 problems
Pagination with query string preservation
Flexible response formats (HTML or JSON)

This type of search functionality is common in many applications but can be complex to implement. Eloquent's query builder makes it more manageable by providing a clean, expressive interface for building even the most sophisticated queries.

Practice Activity

Basic Query Building Exercise

Create a set of queries for a blog system that:

Retrieves all published posts ordered by published date
Finds posts with a specific tag
Gets the 5 most commented posts
Finds posts published in the last month with at least 10 comments
Gets all posts by a specific author that contain a search term in the title or content

Advanced Filtering Exercise

Build a post filtering system that allows filtering by:

Category (including subcategories)
Author
Date range
Tags (posts that have all specified tags)
Minimum comment count
Featured status

Implement this using the when() method for conditional query building, and make it paginated.

Analytics Query Exercise

Create a dashboard query that provides the following analytics:

Total posts, comments, and users
Posts per category (as a count)
Comments per month for the last 6 months
Most active authors (by post count)
Most popular posts (by comment count)
Average comments per post

Use aggregate functions, subqueries, and grouping to build these analytics queries.

Summary

Eloquent provides a powerful query builder for fluent, expressive database queries
Basic retrieval methods include all(), find(), first(), and their variations
Where clauses can be built with a variety of methods for different conditions
Results can be ordered, grouped, and limited with chainable methods
Raw SQL expressions can be used when needed for database-specific operations
Conditional queries can be built using the when() method
Chunking helps process large datasets efficiently
Aggregate functions provide summary statistics about your data
Advanced relationship queries let you efficiently work with connected data
Local and global scopes help encapsulate common query logic
Performance can be optimized through careful selection, eager loading, and indexing

In the next lecture, we'll explore Laravel's database migrations, seeders, and factories, which provide tools for managing database structure and populating it with test data.