Master complex database queries with Laravel Eloquent subqueries, window functions, and advanced SQL techniques for high-performance applications.
Table Of Contents
- Understanding Subqueries in Laravel Eloquent
- Basic Subquery Patterns
- Advanced Subquery Techniques
- Window Functions and Analytics
- Complex Reporting Queries
- Performance Optimization Strategies
- Testing Complex Queries
- Best Practices and Common Pitfalls
Understanding Subqueries in Laravel Eloquent
Subqueries are SQL queries nested within other queries that enable complex data retrieval and manipulation. Laravel's Eloquent ORM provides elegant methods to construct sophisticated subqueries while maintaining readable, maintainable code. This is essential for optimizing Eloquent performance in large datasets.
Subqueries solve problems that would otherwise require multiple database round trips or complex application-level data processing. They're particularly valuable in reporting, analytics, and complex business logic scenarios where single-query solutions offer significant performance benefits.
Basic Subquery Patterns
Where Subqueries
Use subqueries in WHERE clauses to filter records based on related data:
// Find users who have placed orders in the last 30 days
$activeUsers = User::whereHas('orders', function ($query) {
$query->where('created_at', '>=', now()->subDays(30));
})->get();
// Equivalent raw subquery approach
$activeUsers = User::whereIn('id', function ($query) {
$query->select('user_id')
->from('orders')
->where('created_at', '>=', now()->subDays(30))
->distinct();
})->get();
// Find products that have never been ordered
$unorderedProducts = Product::whereNotIn('id', function ($query) {
$query->select('product_id')
->from('order_items')
->whereNotNull('product_id');
})->get();
// Find users with above-average order totals
$highValueUsers = User::whereIn('id', function ($query) {
$query->select('user_id')
->from('orders')
->groupBy('user_id')
->havingRaw('SUM(total_amount) > (SELECT AVG(total_amount) FROM orders)');
})->get();
Select Subqueries
Include calculated values from related tables directly in your SELECT clause:
// Add latest order date to user query
$usersWithLastOrder = User::select([
'users.*',
DB::raw('(SELECT MAX(created_at) FROM orders WHERE orders.user_id = users.id) as last_order_date')
])->get();
// Using Eloquent's selectSub method (Laravel 6+)
$usersWithLastOrder = User::selectSub(
Order::select('created_at')
->whereColumn('user_id', 'users.id')
->latest()
->limit(1),
'last_order_date'
)->get();
// Add order count and total spending
$usersWithStats = User::select([
'users.*',
DB::raw('(SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count'),
DB::raw('(SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE orders.user_id = users.id) as total_spent')
])->get();
// Multiple subqueries with different aggregations
$productStats = Product::select([
'products.*',
DB::raw('(SELECT COUNT(*) FROM order_items WHERE order_items.product_id = products.id) as times_ordered'),
DB::raw('(SELECT COALESCE(SUM(quantity), 0) FROM order_items WHERE order_items.product_id = products.id) as total_quantity_sold'),
DB::raw('(SELECT COALESCE(AVG(quantity), 0) FROM order_items WHERE order_items.product_id = products.id) as avg_quantity_per_order')
])->get();
Advanced Subquery Techniques
Correlated Subqueries
Create subqueries that reference the outer query for complex filtering:
// Find users whose last order was more expensive than their average
$usersWithExpensiveLastOrder = User::whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders as o1')
->whereColumn('o1.user_id', 'users.id')
->where('o1.created_at', '=', function ($subQuery) {
$subQuery->select(DB::raw('MAX(created_at)'))
->from('orders as o2')
->whereColumn('o2.user_id', 'users.id');
})
->where('o1.total_amount', '>', function ($subQuery) {
$subQuery->select(DB::raw('AVG(total_amount)'))
->from('orders as o3')
->whereColumn('o3.user_id', 'users.id');
});
})->get();
// Find products with declining sales (current month vs previous month)
$decliningProducts = Product::whereExists(function ($query) {
$query->select(DB::raw(1))
->from('order_items as oi1')
->join('orders as o1', 'oi1.order_id', '=', 'o1.id')
->whereColumn('oi1.product_id', 'products.id')
->where('o1.created_at', '>=', now()->startOfMonth())
->groupBy('oi1.product_id')
->havingRaw('SUM(oi1.quantity) < (
SELECT COALESCE(SUM(oi2.quantity), 0)
FROM order_items oi2
JOIN orders o2 ON oi2.order_id = o2.id
WHERE oi2.product_id = products.id
AND o2.created_at >= ? AND o2.created_at < ?
)', [
now()->subMonth()->startOfMonth(),
now()->startOfMonth()
]);
})->get();
Subqueries with Joins
Combine subqueries with joins for complex data relationships:
// Get users with their rank based on total spending
$rankedUsers = DB::table('users')
->select([
'users.*',
'user_totals.total_spent',
'user_totals.user_rank'
])
->joinSub(
DB::table('users as u')
->select([
'u.id',
DB::raw('COALESCE(SUM(o.total_amount), 0) as total_spent'),
DB::raw('RANK() OVER (ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) as user_rank')
])
->leftJoin('orders as o', 'u.id', '=', 'o.user_id')
->groupBy('u.id'),
'user_totals',
'users.id',
'=',
'user_totals.id'
)
->orderBy('user_totals.user_rank')
->get();
// Find products and their best-selling month
$productsWithBestMonth = DB::table('products')
->select([
'products.*',
'best_months.best_month',
'best_months.quantity_sold'
])
->joinSub(
DB::table('order_items as oi')
->select([
'oi.product_id',
DB::raw('DATE_FORMAT(o.created_at, "%Y-%m") as best_month'),
DB::raw('SUM(oi.quantity) as quantity_sold'),
DB::raw('RANK() OVER (PARTITION BY oi.product_id ORDER BY SUM(oi.quantity) DESC) as month_rank')
])
->join('orders as o', 'oi.order_id', '=', 'o.id')
->groupBy('oi.product_id', DB::raw('DATE_FORMAT(o.created_at, "%Y-%m")'))
->having('month_rank', 1),
'best_months',
'products.id',
'=',
'best_months.product_id'
)
->get();
Window Functions and Analytics
Ranking and Row Numbers
Use window functions for analytical queries:
// Get top 3 products by sales in each category
$topProductsByCategory = DB::table('products as p')
->select([
'p.*',
'sales_data.total_sold',
'sales_data.category_rank'
])
->joinSub(
DB::table('products as prod')
->select([
'prod.id',
'prod.category_id',
DB::raw('COALESCE(SUM(oi.quantity), 0) as total_sold'),
DB::raw('ROW_NUMBER() OVER (PARTITION BY prod.category_id ORDER BY COALESCE(SUM(oi.quantity), 0) DESC) as category_rank')
])
->leftJoin('order_items as oi', 'prod.id', '=', 'oi.product_id')
->groupBy('prod.id', 'prod.category_id'),
'sales_data',
'p.id',
'=',
'sales_data.id'
)
->where('sales_data.category_rank', '<=', 3)
->orderBy('p.category_id')
->orderBy('sales_data.category_rank')
->get();
// Calculate running totals for orders
$ordersWithRunningTotal = DB::table('orders')
->select([
'orders.*',
DB::raw('SUM(total_amount) OVER (ORDER BY created_at) as running_total'),
DB::raw('AVG(total_amount) OVER (ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7_days')
])
->orderBy('created_at')
->get();
Percentiles and Statistical Functions
// Find users in different spending percentiles
$userSpendingPercentiles = DB::table('users as u')
->select([
'u.*',
'spending_stats.total_spent',
'spending_stats.spending_percentile',
DB::raw('CASE
WHEN spending_stats.spending_percentile >= 0.9 THEN "Top 10%"
WHEN spending_stats.spending_percentile >= 0.75 THEN "Top 25%"
WHEN spending_stats.spending_percentile >= 0.5 THEN "Above Average"
ELSE "Below Average"
END as spending_tier')
])
->joinSub(
DB::table('users as users_inner')
->select([
'users_inner.id',
DB::raw('COALESCE(SUM(o.total_amount), 0) as total_spent'),
DB::raw('PERCENT_RANK() OVER (ORDER BY COALESCE(SUM(o.total_amount), 0)) as spending_percentile')
])
->leftJoin('orders as o', 'users_inner.id', '=', 'o.user_id')
->groupBy('users_inner.id'),
'spending_stats',
'u.id',
'=',
'spending_stats.id'
)
->get();
Complex Reporting Queries
Cohort Analysis
Create cohort analysis reports using subqueries:
// User retention cohort analysis
$cohortAnalysis = DB::table(DB::raw('(
SELECT
u.id as user_id,
DATE_FORMAT(u.created_at, "%Y-%m") as cohort_month,
DATE_FORMAT(o.created_at, "%Y-%m") as order_month,
PERIOD_DIFF(
DATE_FORMAT(o.created_at, "%Y%m"),
DATE_FORMAT(u.created_at, "%Y%m")
) as period_number
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= ?
) as cohort_data', [now()->subYear()]))
->select([
'cohort_month',
'period_number',
DB::raw('COUNT(DISTINCT user_id) as users'),
DB::raw('COUNT(DISTINCT user_id) / FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (PARTITION BY cohort_month ORDER BY period_number) * 100 as retention_rate')
])
->groupBy('cohort_month', 'period_number')
->orderBy('cohort_month')
->orderBy('period_number')
->get();
Revenue Analytics
Build comprehensive revenue reporting queries:
// Monthly revenue analysis with growth rates
$revenueAnalysis = DB::table(DB::raw('(
SELECT
DATE_FORMAT(created_at, "%Y-%m") as month,
SUM(total_amount) as monthly_revenue,
COUNT(*) as order_count,
AVG(total_amount) as avg_order_value
FROM orders
WHERE created_at >= ?
GROUP BY DATE_FORMAT(created_at, "%Y-%m")
) as monthly_data', [now()->subYear()]))
->select([
'month',
'monthly_revenue',
'order_count',
'avg_order_value',
DB::raw('LAG(monthly_revenue) OVER (ORDER BY month) as prev_month_revenue'),
DB::raw('(monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY month)) / LAG(monthly_revenue) OVER (ORDER BY month) * 100 as growth_rate'),
DB::raw('SUM(monthly_revenue) OVER (ORDER BY month) as cumulative_revenue')
])
->orderBy('month')
->get();
// Product performance with ranking
$productPerformance = DB::table('products as p')
->select([
'p.name',
'p.category_id',
'sales_metrics.total_revenue',
'sales_metrics.total_quantity',
'sales_metrics.avg_price',
'sales_metrics.revenue_rank',
'sales_metrics.quantity_rank',
DB::raw('(sales_metrics.total_revenue / SUM(sales_metrics.total_revenue) OVER ()) * 100 as revenue_percentage')
])
->joinSub(
DB::table('order_items as oi')
->select([
'oi.product_id',
DB::raw('SUM(oi.price * oi.quantity) as total_revenue'),
DB::raw('SUM(oi.quantity) as total_quantity'),
DB::raw('AVG(oi.price) as avg_price'),
DB::raw('RANK() OVER (ORDER BY SUM(oi.price * oi.quantity) DESC) as revenue_rank'),
DB::raw('RANK() OVER (ORDER BY SUM(oi.quantity) DESC) as quantity_rank')
])
->groupBy('oi.product_id'),
'sales_metrics',
'p.id',
'=',
'sales_metrics.product_id'
)
->orderBy('sales_metrics.revenue_rank')
->get();
Performance Optimization Strategies
Indexed Subqueries
Optimize subquery performance with proper indexing:
// Ensure proper indexes exist for subquery performance
Schema::table('orders', function (Blueprint $table) {
$table->index(['user_id', 'created_at']); // For user-based date queries
$table->index(['created_at', 'total_amount']); // For date-based amount queries
$table->index(['user_id', 'total_amount']); // For user spending queries
});
Schema::table('order_items', function (Blueprint $table) {
$table->index(['product_id', 'quantity']); // For product sales queries
$table->index(['order_id', 'product_id']); // For order-product joins
});
// Use covering indexes when possible
Schema::table('users', function (Blueprint $table) {
$table->index(['created_at', 'id', 'email']); // Covering index for user queries
});
Query Result Caching
Cache expensive subquery results:
<?php
namespace App\Services;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
class AnalyticsService
{
public function getUserSpendingRanks(): Collection
{
return Cache::remember('user_spending_ranks', 3600, function () {
return DB::table('users as u')
->select([
'u.id',
'u.name',
'u.email',
'spending_data.total_spent',
'spending_data.spending_rank'
])
->joinSub(
DB::table('users as users_inner')
->select([
'users_inner.id',
DB::raw('COALESCE(SUM(o.total_amount), 0) as total_spent'),
DB::raw('RANK() OVER (ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) as spending_rank')
])
->leftJoin('orders as o', 'users_inner.id', '=', 'o.user_id')
->groupBy('users_inner.id'),
'spending_data',
'u.id',
'=',
'spending_data.id'
)
->orderBy('spending_data.spending_rank')
->get();
});
}
public function getProductSalesAnalytics(string $period = 'last_30_days'): Collection
{
$cacheKey = "product_sales_analytics_{$period}";
return Cache::remember($cacheKey, 1800, function () use ($period) {
$dateFilter = match($period) {
'last_7_days' => now()->subDays(7),
'last_30_days' => now()->subDays(30),
'last_90_days' => now()->subDays(90),
'last_year' => now()->subYear(),
default => now()->subDays(30),
};
return DB::table('products as p')
->select([
'p.id',
'p.name',
'analytics.total_revenue',
'analytics.total_quantity',
'analytics.order_count',
'analytics.revenue_rank'
])
->joinSub(
DB::table('order_items as oi')
->join('orders as o', 'oi.order_id', '=', 'o.id')
->select([
'oi.product_id',
DB::raw('SUM(oi.price * oi.quantity) as total_revenue'),
DB::raw('SUM(oi.quantity) as total_quantity'),
DB::raw('COUNT(DISTINCT oi.order_id) as order_count'),
DB::raw('RANK() OVER (ORDER BY SUM(oi.price * oi.quantity) DESC) as revenue_rank')
])
->where('o.created_at', '>=', $dateFilter)
->groupBy('oi.product_id'),
'analytics',
'p.id',
'=',
'analytics.product_id'
)
->orderBy('analytics.revenue_rank')
->get();
});
}
}
Testing Complex Queries
Unit Testing Subqueries
Create comprehensive tests for complex queries:
<?php
namespace Tests\Unit\Services;
use Tests\TestCase;
use App\Models\User;
use App\Models\Order;
use App\Models\Product;
use App\Models\OrderItem;
use App\Services\AnalyticsService;
class AnalyticsServiceTest extends TestCase
{
protected AnalyticsService $analyticsService;
protected function setUp(): void
{
parent::setUp();
$this->analyticsService = new AnalyticsService();
}
public function test_user_spending_ranks_calculated_correctly(): void
{
// Create test data
$user1 = User::factory()->create();
$user2 = User::factory()->create();
$user3 = User::factory()->create();
// User 1: $300 total
Order::factory()->create(['user_id' => $user1->id, 'total_amount' => 100]);
Order::factory()->create(['user_id' => $user1->id, 'total_amount' => 200]);
// User 2: $500 total
Order::factory()->create(['user_id' => $user2->id, 'total_amount' => 500]);
// User 3: $0 total (no orders)
$ranks = $this->analyticsService->getUserSpendingRanks();
$this->assertCount(3, $ranks);
// Check ranking order (highest spender first)
$this->assertEquals($user2->id, $ranks[0]->id);
$this->assertEquals(1, $ranks[0]->spending_rank);
$this->assertEquals(500, $ranks[0]->total_spent);
$this->assertEquals($user1->id, $ranks[1]->id);
$this->assertEquals(2, $ranks[1]->spending_rank);
$this->assertEquals(300, $ranks[1]->total_spent);
$this->assertEquals($user3->id, $ranks[2]->id);
$this->assertEquals(3, $ranks[2]->spending_rank);
$this->assertEquals(0, $ranks[2]->total_spent);
}
public function test_product_analytics_with_date_filtering(): void
{
$product1 = Product::factory()->create();
$product2 = Product::factory()->create();
// Recent orders (within last 30 days)
$recentOrder = Order::factory()->create(['created_at' => now()->subDays(10)]);
OrderItem::factory()->create([
'order_id' => $recentOrder->id,
'product_id' => $product1->id,
'quantity' => 2,
'price' => 100
]);
// Old orders (outside last 30 days)
$oldOrder = Order::factory()->create(['created_at' => now()->subDays(60)]);
OrderItem::factory()->create([
'order_id' => $oldOrder->id,
'product_id' => $product2->id,
'quantity' => 5,
'price' => 50
]);
$analytics = $this->analyticsService->getProductSalesAnalytics('last_30_days');
$this->assertCount(1, $analytics); // Only recent product should appear
$this->assertEquals($product1->id, $analytics[0]->id);
$this->assertEquals(200, $analytics[0]->total_revenue); // 2 * 100
$this->assertEquals(2, $analytics[0]->total_quantity);
}
}
Best Practices and Common Pitfalls
Query Optimization Guidelines
Follow these best practices for efficient subqueries:
// Good: Use EXISTS instead of IN when checking for existence
$usersWithOrders = User::whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereColumn('orders.user_id', 'users.id');
})->get();
// Avoid: Using IN with large subquery results
$usersWithOrders = User::whereIn('id', function ($query) {
$query->select('user_id')->from('orders'); // Can return millions of IDs
})->get();
// Good: Use proper column selection in subqueries
$usersWithLatestOrder = User::selectSub(
Order::select('created_at')
->whereColumn('user_id', 'users.id')
->latest()
->limit(1),
'latest_order_date'
)->get();
// Good: Use LIMIT in subqueries when appropriate
$topSpenders = User::whereIn('id', function ($query) {
$query->select('user_id')
->from('orders')
->groupBy('user_id')
->orderByRaw('SUM(total_amount) DESC')
->limit(100); // Limit results for performance
})->get();
Mastering subqueries and advanced SQL techniques in Laravel Eloquent enables you to build sophisticated applications with efficient database queries. These patterns are essential for high-performance Laravel applications that need to handle complex reporting and analytics workloads.
Share this article
Add Comment
No comments yet. Be the first to comment!