Laravel Framework

Laravel Eloquent Advanced Queries

18 min Lesson 21 of 45

Laravel Eloquent Advanced Queries

While basic Eloquent queries are powerful, Laravel provides advanced query capabilities for complex data retrieval scenarios. This lesson explores sophisticated querying techniques that will help you build efficient database operations.

Query Builder Advanced Techniques

Beyond basic where clauses, Laravel's query builder offers advanced methods for complex conditions:

Advanced Where Clauses:

// Where with subqueries
$users = User::where('votes', '>', function ($query) {
    $query->selectRaw('avg(votes)')
          ->from('users')
          ->where('status', 'active');
})->get();

// whereColumn - comparing two columns
$products = Product::whereColumn('sale_price', '<', 'regular_price')->get();

// whereIn with subquery
$activeUsers = User::whereIn('id', function ($query) {
    $query->select('user_id')
          ->from('orders')
          ->where('created_at', '>=', now()->subMonth());
})->get();

// whereBetween with dates
$recentOrders = Order::whereBetween('created_at', [
    now()->subDays(7),
    now()
])->get();

// whereDate, whereMonth, whereDay, whereYear
$todayOrders = Order::whereDate('created_at', today())->get();
$decemberSales = Sale::whereMonth('created_at', 12)->get();

Performance Tip: When using subqueries in where clauses, ensure the subquery's table has proper indexes on the columns being queried to maintain good performance.

Raw Expressions and SQL Injection Safety

Sometimes you need raw SQL for complex operations. Laravel provides safe methods to include raw expressions:

Using Raw Expressions Safely:

// selectRaw for calculated columns
$users = User::selectRaw('name, email, age * 12 as months_lived')
             ->get();

// whereRaw with bindings (prevents SQL injection)
$orders = Order::whereRaw('price > IF(status = "premium", ?, ?)', [100, 50])
               ->get();

// orderByRaw for custom sorting
$products = Product::orderByRaw('FIELD(category, "featured", "new", "sale")')
                   ->get();

// havingRaw for aggregate conditions
$results = DB::table('orders')
             ->select('customer_id', DB::raw('SUM(price) as total'))
             ->groupBy('customer_id')
             ->havingRaw('SUM(price) > ?', [1000])
             ->get();

// DB::raw for complex expressions
$users = User::select(DB::raw('COUNT(*) as user_count, status'))
             ->where('status', '<>', 'deleted')
             ->groupBy('status')
             ->get();

Security Warning: Never concatenate user input directly into raw SQL. Always use parameter bindings (the ? placeholders) to prevent SQL injection attacks.

Chunking Large Result Sets

When dealing with thousands of records, loading them all into memory can cause issues. Chunking processes results in smaller batches:

Chunk and Cursor Methods:

// Chunk - processes in batches
User::where('active', true)->chunk(200, function ($users) {
    foreach ($users as $user) {
        // Process each user
        $user->sendMonthlyReport();
    }
});

// chunkById - more efficient for large datasets (uses ID ordering)
Order::where('status', 'pending')
     ->chunkById(100, function ($orders) {
         foreach ($orders as $order) {
             $order->process();
         }
     }, $column = 'id');

// Cursor - lazy loads one at a time (memory efficient)
foreach (User::where('votes', '>', 100)->cursor() as $user) {
    // Only one user loaded in memory at a time
    $user->calculateStatistics();
}

// lazy - similar to cursor but with better control
User::where('active', true)->lazy()->each(function ($user) {
    // Process user
    $user->updateMetrics();
});

// lazyById - even more efficient for very large datasets
User::where('subscribed', true)
    ->lazyById(500)
    ->each(function ($user) {
        $user->sendNewsletter();
    });

Choosing the Right Method: Use chunk() for batch processing, cursor() or lazy() when processing one at a time, and chunkById() or lazyById() for the best performance on very large tables.

Aggregate Functions and Advanced Grouping

Laravel provides methods for statistical calculations and complex grouping:

Aggregates and Statistics:

// Basic aggregates
$totalOrders = Order::count();
$averagePrice = Order::avg('price');
$maxPrice = Order::max('price');
$minPrice = Order::min('price');
$totalRevenue = Order::sum('price');

// Grouping with aggregates
$salesByCategory = Product::select('category', DB::raw('COUNT(*) as count'))
                          ->groupBy('category')
                          ->get();

// Having clauses with aggregates
$activeCustomers = Order::select('customer_id', DB::raw('COUNT(*) as order_count'))
                        ->groupBy('customer_id')
                        ->having('order_count', '>', 5)
                        ->get();

// Multiple groupBy columns
$monthlySales = Order::select(
    DB::raw('YEAR(created_at) as year'),
    DB::raw('MONTH(created_at) as month'),
    DB::raw('SUM(price) as revenue')
)
->groupBy('year', 'month')
->orderBy('year', 'desc')
->orderBy('month', 'desc')
->get();

// Conditional aggregates
$stats = Order::selectRaw('
    COUNT(*) as total_orders,
    SUM(CASE WHEN status = "completed" THEN 1 ELSE 0 END) as completed,
    SUM(CASE WHEN status = "pending" THEN 1 ELSE 0 END) as pending,
    AVG(price) as average_price
')->first();

Conditional Clauses and Dynamic Queries

Build queries dynamically based on conditions without messy if/else statements:

When and Unless Methods:

// when() - adds clause only if condition is true
$sortBy = request('sort_by');
$products = Product::when($sortBy, function ($query, $sortBy) {
    return $query->orderBy($sortBy);
})->get();

// when() with default callback
$role = request('role');
$users = User::when($role, function ($query, $role) {
    // Applied when role exists
    return $query->where('role', $role);
}, function ($query) {
    // Applied when role doesn't exist (default)
    return $query->where('role', 'user');
})->get();

// unless() - opposite of when()
$showInactive = request('show_inactive');
$users = User::unless($showInactive, function ($query) {
    // Only applied when showInactive is false
    return $query->where('active', true);
})->get();

// Practical search example
$search = request('search');
$category = request('category');
$minPrice = request('min_price');
$maxPrice = request('max_price');

$products = Product::query()
    ->when($search, function ($query, $search) {
        $query->where('name', 'like', "%{$search}%");
    })
    ->when($category, function ($query, $category) {
        $query->where('category_id', $category);
    })
    ->when($minPrice, function ($query, $minPrice) {
        $query->where('price', '>=', $minPrice);
    })
    ->when($maxPrice, function ($query, $maxPrice) {
        $query->where('price', '<=', $maxPrice);
    })
    ->get();

Clean Code: Using when() and unless() keeps your query building code clean and readable, avoiding deeply nested if statements.

Subqueries in Select Statements

Include calculated data from related tables efficiently using subqueries:

Select Subqueries:

// Add subquery results as columns
$users = User::select(['name', 'email'])
    ->selectSub(function ($query) {
        $query->from('orders')
              ->whereColumn('user_id', 'users.id')
              ->selectRaw('COUNT(*)');
    }, 'orders_count')
    ->selectSub(function ($query) {
        $query->from('orders')
              ->whereColumn('user_id', 'users.id')
              ->selectRaw('SUM(price)');
    }, 'total_spent')
    ->get();

// Using addSelect for additional subqueries
$products = Product::select('id', 'name', 'price')
    ->addSelect([
        'reviews_avg' => Review::selectRaw('AVG(rating)')
            ->whereColumn('product_id', 'products.id'),
        'reviews_count' => Review::selectRaw('COUNT(*)')
            ->whereColumn('product_id', 'products.id')
    ])
    ->get();

// Subquery ordering
$users = User::orderBy(
    Order::selectRaw('COUNT(*)')->whereColumn('user_id', 'users.id'),
    'desc'
)->get();

Practice Exercise 1: Create a query that retrieves all products with their category name, average rating, review count, and total sales. Filter to only show products that have more than 10 reviews and an average rating above 4.0. Order by total sales descending.

Practice Exercise 2: Build a dynamic search query for a blog that accepts optional filters: keyword (searches title and content), category_id, author_id, published_after date, and published_before date. Use the when() method to conditionally apply each filter.

Practice Exercise 3: Write a script that processes all users in chunks of 500, calculates their total purchases, and updates a "total_spent" column. Use chunking to handle potentially millions of users efficiently without running out of memory.

Summary

Advanced Eloquent queries give you the power to handle complex data retrieval efficiently:

Subqueries: Enable complex filtering and calculated columns
Raw expressions: Provide SQL flexibility with security through bindings
Chunking: Processes large datasets without memory issues
Conditional clauses: Build dynamic queries cleanly with when() and unless()
Aggregates: Calculate statistics directly in the database

In the next lesson, we'll explore API Resources and how to transform Eloquent models for API responses.