Laravel多关联查询N+1问题优化方案求助

方案1：重写Eloquent Builder合并重复关联查询 #

Laravel默认预加载逻辑会按模型$with定义的顺序逐个处理关联，遇到同表关联时会重复发起查询。我们可以通过扩展Eloquent\Builder类，在预加载阶段自动合并相同关联的请求。

实现步骤： #

1. 创建自定义Builder类，重写预加载核心方法合并同表关联：

<?php

namespace App\Database\Eloquent;

use Illuminate\Database\Eloquent\Builder as BaseBuilder;
use Illuminate\Database\Eloquent\Relations\Relation;

class Builder extends BaseBuilder
{
    /**
     * 重写预加载逻辑，合并同表关联查询
     *
     * @param  array  $models
     * @return void
     */
    protected function eagerLoadRelations(array $models)
    {
        $mergedRelations = $this->mergeDuplicateRelations($this->eagerLoad);
        
        foreach ($mergedRelations as $name => $constraints) {
            if (str_contains($name, '.')) {
                $this->eagerLoadNestedRelation($models, $name, $constraints);
                continue;
            }

            $relation = $this->getRelation($name);
            $relation->addConstraints();
            
            $models = $relation->initRelation($models, $name);
            $results = $relation->getEager();
            $models = $relation->match($models, $results, $name);
        }
    }

    /**
     * 合并重复的同表关联请求
     *
     * @param  array  $eagerLoad
     * @return array
     */
    protected function mergeDuplicateRelations(array $eagerLoad): array
    {
        $merged = [];
        $tableMap = [];

        foreach ($eagerLoad as $name => $constraints) {
            $relation = $this->getRelation($name);
            $table = $relation->getRelated()->getTable();

            if (!isset($tableMap[$table])) {
                $tableMap[$table] = $name;
                $merged[$name] = $constraints;
            } else {
                $existingName = $tableMap[$table];
                $merged[$existingName] = function ($query) use ($constraints) {
                    $constraints($query);
                };
            }
        }

        return $merged;
    }
}

2. 在模型基类中绑定自定义Builder：

<?php

namespace App\Models;

use App\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model as BaseModel;

abstract class Model extends BaseModel
{
    public function newEloquentBuilder($query)
    {
        return new Builder($query);
    }
}

方案2：全局预加载管理器统一处理 #

通过服务提供者收集所有模型的$with关联，在查询前统一去重并合并同表关联，避免重复查询。

实现步骤： #

1. 创建全局预加载管理器：

<?php

namespace App\Support;

use Illuminate\Database\Eloquent\Model;

class PreloadManager
{
    protected array $modelRelations = [];

    public function registerModel(string $modelClass)
    {
        $model = new $modelClass;
        $this->modelRelations[$modelClass] = $model->with;
    }

    public function getMergedRelations(string $modelClass): array
    {
        $relations = $this->modelRelations[$modelClass] ?? [];
        $merged = [];
        $tableMap = [];

        foreach ($relations as $relation) {
            $parts = explode('.', $relation);
            $currentModel = new $modelClass;
            
            foreach ($parts as $part) {
                $relationInstance = $currentModel->{$part}();
                $table = $relationInstance->getRelated()->getTable();
                
                if (!isset($tableMap[$table])) {
                    $tableMap[$table] = $relation;
                    $merged[] = $relation;
                }
                
                $currentModel = $relationInstance->getRelated();
            }
        }

        return $merged;
    }
}

2. 在AppServiceProvider中注册并生效：

<?php

namespace App\Providers;

use App\Support\PreloadManager;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function register()
    {
        $this->app->singleton(PreloadManager::class, function ($app) {
            $manager = new PreloadManager;
            
            // 自动注册所有Models目录下的模型
            $models = glob(app_path('Models/*.php'));
            foreach ($models as $model) {
                $class = 'App\\Models\\' . basename($model, '.php');
                $manager->registerModel($class);
            }
            
            return $manager;
        });
    }

    public function boot()
    {
        $manager = $this->app->make(PreloadManager::class);
        
        // 监听模型初始化事件，替换全局预加载为合并后的列表
        Model::creating(function ($model) use ($manager) {
            $model->with = $manager->getMergedRelations(get_class($model));
        });
    }
}

方案3：关联分析工具生成最优加载顺序 #

如果需要更智能的合并逻辑，可以编写工具类分析关联依赖，自动生成最优预加载顺序，确保同表关联仅查询一次。

核心实现框架： #

<?php

namespace App\Support;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\Relation;

class RelationAnalyzer
{
    protected array $relationTree = [];

    public function analyze(string $modelClass, ?string $parentRelation = null)
    {
        $model = new $modelClass;
        $relations = $model->with;

        foreach ($relations as $relation) {
            $fullPath = $parentRelation ? "{$parentRelation}.{$relation}" : $relation;
            $relationInstance = $model->{$relation}();
            $table = $relationInstance->getRelated()->getTable();

            $this->relationTree[$table][] = [
                'path' => $fullPath,
                'constraints' => $relationInstance->getQuery()->getQuery()->wheres ?? [],
            ];

            // 递归分析子关联的依赖
            $this->analyze(get_class($relationInstance->getRelated()), $fullPath);
        }
    }

    public function getOptimalPreloads(): array
    {
        $optimal = [];

        foreach ($this->relationTree as $table => $entries) {
            $mergedPath = $entries[0]['path'];
            $mergedConstraints = function ($query) use ($entries) {
                foreach ($entries as $entry) {
                    foreach ($entry['constraints'] as $where) {
                        $query->where($where['column'], $where['operator'], $where['value']);
                    }
                }
            };

            $optimal[$mergedPath] = $mergedConstraints;
        }

        return $optimal;
    }
}

使用示例： #

$analyzer = new RelationAnalyzer;
$analyzer->analyze(User::class);
$optimalPreloads = $analyzer->getOptimalPreloads();

// 直接使用合并后的预加载查询
User::with($optimalPreloads)->get();

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