Laravel Eloquent关联预加载与聚合值计算优化方案咨询

Hey there! Let's break down how to efficiently load all those nested aggregations for today's data in Laravel Eloquent—this is a common scenario when dealing with multi-level relationships and performance-critical dashboards. Here are the best approaches I’ve used in similar projects:

1. Eager Loading with Reusable Scopes (Best for Maintainability) #

The cleanest way to handle this is to encapsulate your aggregation logic into model scopes, then eager load all nested relationships with their respective aggregates. This avoids N+1 query issues and keeps your code organized.

First, add scopes to each model to calculate today's aggregates:

Keyword Model Scope #

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;

public function scopeWithTodayAggregations(Builder $query)
{
    $today = Carbon::today();
    return $query->select([
        'id', 'name', 'group_id',
        // Valid Click Ads Aggregates
        DB::raw('COALESCE(SUM(valid_click_ads.revenue), 0) as total_revenue'),
        DB::raw('COALESCE(SUM(CASE WHEN valid_click_ads.tq != -1 THEN valid_click_ads.tq ELSE 0 END), 0) as total_tq'),
        DB::raw('COALESCE(COUNT(valid_click_ads.tq), 0) as tq_count'),
        DB::raw('COALESCE(SUM(CASE WHEN valid_click_ads.tq != -1 THEN 1 ELSE 0 END), 0) as valid_tq_count'),
        DB::raw('COALESCE(SUM(valid_click_ads.clicks), 0) as total_valid_clicks'),
    ])
    ->leftJoin('valid_click_ads', 'keywords.id', '=', 'valid_click_ads.keyword_id')
    ->whereDate('valid_click_ads.created_at', $today)
    ->orWhereNull('valid_click_ads.created_at') // Handle entries with no valid clicks
    ->groupBy('keywords.id', 'keywords.name', 'keywords.group_id');
}

Group Model Scope #

public function scopeWithTodayAggregations(Builder $query)
{
    $today = Carbon::today();
    return $query->select([
        'id', 'name', 'campaign_id',
        // Ad Platform Aggregates
        DB::raw('COALESCE(SUM(facebook_ads.spend), 0) as total_facebook_spend'),
        DB::raw('COALESCE(SUM(yahoo_ads.spend), 0) as total_yahoo_spend'),
        DB::raw('COALESCE(SUM(google_ads.spend), 0) as total_google_spend'),
        DB::raw('COALESCE(SUM(facebook_ads.impressions), 0) as total_facebook_impressions'),
        DB::raw('COALESCE(SUM(yahoo_ads.impressions), 0) as total_yahoo_impressions'),
        DB::raw('COALESCE(SUM(google_ads.impressions), 0) as total_google_impressions'),
        DB::raw('COALESCE(SUM(facebook_ads.clicks), 0) as total_facebook_clicks'),
        DB::raw('COALESCE(SUM(yahoo_ads.clicks), 0) as total_yahoo_clicks'),
        DB::raw('COALESCE(SUM(google_ads.clicks), 0) as total_google_clicks'),
        // Valid Click Ads Aggregates
        DB::raw('COALESCE(SUM(valid_click_ads.revenue), 0) as total_revenue'),
        DB::raw('COALESCE(SUM(CASE WHEN valid_click_ads.tq != -1 THEN valid_click_ads.tq ELSE 0 END), 0) as total_tq'),
        DB::raw('COALESCE(COUNT(valid_click_ads.tq), 0) as tq_count'),
        DB::raw('COALESCE(SUM(CASE WHEN valid_click_ads.tq != -1 THEN 1 ELSE 0 END), 0) as valid_tq_count'),
        DB::raw('COALESCE(SUM(valid_click_ads.clicks), 0) as total_valid_clicks'),
    ])
    ->leftJoin('facebook_ads', 'groups.id', '=', 'facebook_ads.group_id')
    ->leftJoin('yahoo_ads', 'groups.id', '=', 'yahoo_ads.group_id')
    ->leftJoin('google_ads', 'groups.id', '=', 'google_ads.group_id')
    ->leftJoin('valid_click_ads', 'groups.id', '=', 'valid_click_ads.group_id')
    ->whereDate('facebook_ads.created_at', $today)->orWhereNull('facebook_ads.created_at')
    ->whereDate('yahoo_ads.created_at', $today)->orWhereNull('yahoo_ads.created_at')
    ->whereDate('google_ads.created_at', $today)->orWhereNull('google_ads.created_at')
    ->whereDate('valid_click_ads.created_at', $today)->orWhereNull('valid_click_ads.created_at')
    ->groupBy('groups.id', 'groups.name', 'groups.campaign_id');
}

Repeat this pattern for Campaign and Website models, adjusting the joins and group by clauses to match their parent relationships.

Final Query #

Now you can eager load all nested relationships with their aggregates in one go:

$websites = Website::query()
    ->whereDate('created_at', Carbon::today())
    ->withTodayAggregations()
    ->with([
        'campaigns' => function ($query) {
            $query->whereDate('created_at', Carbon::today())
                ->withTodayAggregations()
                ->with([
                    'groups' => function ($query) {
                        $query->whereDate('created_at', Carbon::today())
                            ->withTodayAggregations()
                            ->with(['keywords' => function ($query) {
                                $query->whereDate('created_at', Carbon::today())
                                    ->withTodayAggregations();
                            }]);
                    }
                ]);
        }
    ])
    ->get();

2. Using withAggregate (Laravel 8.40+) #

If you’re on a newer Laravel version, you can use the withAggregate method to define individual aggregation relationships, which feels more "Eloquent-native":

// In Website model
public function totalFacebookSpend()
{
    return $this->hasManyThrough(FacebookAd::class, Campaign::class)
        ->whereDate('created_at', Carbon::today())
        ->selectRaw('SUM(spend) as aggregate')
        ->groupBy('website_id');
}

// Then in your query
$websites = Website::query()
    ->whereDate('created_at', Carbon::today())
    ->withAggregate('totalFacebookSpend', 'aggregate')
    ->withAggregate('totalYahooSpend', 'aggregate')
    // Add all other aggregates as separate relationships
    ->with([
        'campaigns' => fn($q) => $q->whereDate('created_at', Carbon::today())->withAggregate('campaignTotalRevenue', 'aggregate')
    ])
    ->get();

This is great if you only need a few aggregates, but can get verbose if you need all the metrics listed.

3. Database Views (For Extreme Performance) #

If you’re dealing with massive datasets and need maximum speed, create database views that pre-calculate all today’s aggregates for each level. For example, a website_daily_aggregates view that joins all necessary tables and computes the sums/counts.

Once the view is created, you can map it to a model (e.g., WebsiteDailyAggregate) and eager load it with your Website model. This avoids runtime calculations and leverages database indexing for speed.

Critical Optimization Tips #

• Add Indexes: Create composite indexes on foreign keys + created_at (e.g., idx_valid_click_ads_keyword_created on valid_click_ads(keyword_id, created_at)) to speed up joins and filters.
• Use COALESCE: As shown in the scopes, this ensures NULL values (from entries with no ads/clicks) are converted to 0, making frontend handling easier.
• Pagination: If you have thousands of records, use paginate() instead of get() to avoid loading all data into memory at once.

内容的提问来源于stack exchange，提问作者LucyTurtle