Advanced Query Optimization with Subqueries and CTEs in Laravel

Photo by Jan Antonin Kolar on Unsplash

Scenario

Imagine a reporting feature where you need to display data aggregated from a massive database while keeping queries efficient and readable.

1. Using Subqueries in Eloquent

Subqueries allow you to calculate derived data directly within your main query.

Example: Calculating Last Purchase Date Per User

use Illuminate\Support\Facades\DB;

// Fetch users with their last purchase date
$users = User::select([
    'users.*',
    DB::raw('(SELECT MAX(created_at) FROM orders WHERE orders.user_id = users.id) AS last_purchase_date')
])->get();

foreach ($users as $user) {
    echo $user->last_purchase_date; // Access the derived column
}

2. Leveraging Common Table Expressions (CTEs)

CTEs help in simplifying complex queries, improving readability and maintainability.

Example: Users with Total Purchases and Last Purchase Date

use Illuminate\Support\Facades\DB;

// Define a CTE for user purchases
$usersWithPurchases = DB::table('users')
    ->withExpression('user_purchases', function ($query) {
        $query->select('user_id', DB::raw('SUM(total) as total_purchases'), DB::raw('MAX(created_at) as last_purchase_date'))
            ->from('orders')
            ->groupBy('user_id');
    })
    ->select('users.*', 'user_purchases.total_purchases', 'user_purchases.last_purchase_date')
    ->leftJoin('user_purchases', 'users.id', '=', 'user_purchases.user_id')
    ->get();

foreach ($usersWithPurchases as $user) {
    echo "{$user->name} - Total: {$user->total_purchases}, Last: {$user->last_purchase_date}\n";
}

3. Using Relationships with Advanced Queries

Combine relationships with subqueries for powerful, reusable functionality.

Example: Most Expensive Order Per User

// Define a subquery for most expensive order per user
$users = User::select([
    'users.*',
    DB::raw('(SELECT MAX(total) FROM orders WHERE orders.user_id = users.id) AS max_order_total')
])->with('orders')
  ->get();

foreach ($users as $user) {
    echo "{$user->name} - Max Order Total: {$user->max_order_total}\n";
}

4. Optimized Pagination with Chunking or Cursor Pagination

When working with millions of records, use cursor pagination or chunking instead of traditional pagination.

Cursor Pagination

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

foreach ($users as $user) {
    echo $user->name;
}

Chunking

User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        echo $user->name;
    }
});

Why This is Advanced

• Subqueries and CTEs require a deep understanding of SQL to use effectively.
• Combining these techniques with Laravel’s ORM ensures performance while maintaining readability.
• These approaches are critical when dealing with reporting, analytics, or applications with large datasets.

Happy coding 😊

💡 Let’s connect and stay in touch!

🌐 Portfolio: mohasin.dev
💼 LinkedIn: linkedin.com/in/mohasin-dev
👨‍💻 GitHub: github.com/mohasin-dev
🤝 ADPList: adplist.org/mentors/mohasin-hossain