Laravel中如何预加载原始与复制赛事的聚合赛程？

背景与需求 #

正在处理支持赛事复制的数据库设计，需要高效查询原始赛事及其复制赛事的聚合赛程，同时支持对赛程进行过滤、排序等额外查询操作，且要避免O(n)级别的查询复杂度。

数据库结构 #

competitions: id, name, synchronized_id
fixtures: id, competition_id  

业务场景 #

• 赛事Y为原始赛事（synchronized_id字段为null）
• 赛事X的synchronized_id = Y，属于Y的复制赛事
• 需要加载Y自身及所有关联复制赛事的全部赛程，同时支持对赛程统一应用过滤、排序等操作

当前Eloquent关联 #

// Competition模型关联定义
Competition → hasOne('synchronizedTo', Competition::class)  
Competition → hasMany(Fixture::class)  

核心目标 #

• 查询所有原始赛事，聚合自身及关联复制赛事的赛程数据
• 对聚合后的赛程统一执行子查询（过滤、排序、分页等）
• 避免因赛事数量增长导致的大量重复查询，保证查询效率

当前方案与问题 #

当前实现思路 #

1. 收集原始赛事ID及其关联复制赛事ID，存入数组[original_id, synchronized_to_id]
2. 循环每个赛事分组，单独查询对应赛程数据

存在的问题 #

随着赛事数量增加，会产生大量独立查询，效率低下；尝试用预加载with('fixtures')无法实现赛程聚合，且合并后的赛程无法统一排序（比如示例中合并后的赛程日期排序混乱）

已尝试方案 #

• 使用预加载with('fixtures')，但无法聚合原始赛事与复制赛事的赛程数据
• 尚未深入尝试自定义关联或原生SQL集成方案

当前代码实现 #

模型关联代码 #

class Competition extends Model
{
    public function synchronizedTo(): HasOne
    {
        return $this->hasOne(Competition::class, 'synchronized_id');
    }
    
    public function fixtures(): HasMany
    {
        return $this->hasMany(Fixture::class);
    }
}

按赛事分组的赛程服务方法 #

private function groupByCompetition(array $data): array
{
    $competitions = $this->competitionRepository
        ->newQuery()
        ->withoutSynchronization()
        ->with(['synchronizedTo' => fn($q) => $q->select(['id', 'synchronized_id'])])
        ->select(['id', 'synchronized_id', 'name', 'uuid'])
        ->orderByDesc('geo_country_id')
        ->orderByDesc('created_at')
        ->take(10)
        ->get();

    $fixturesByCompetition = [];
    
    foreach ($competitions as $competition) {
        $fixturesByCompetition[$competition->id] = [
            'ids' => [],
            'name' => $competition->name,
            'uuid' => $competition->uuid
        ];
        
        $ref = &$fixturesByCompetition[$competition->id]['ids'];
        $ref[] = $competition->getKey();
        
        if (isset($competition->synchronizedTo)) {
            $ref[] = $competition->synchronizedTo->getKey();
        }
    }

    return collect($fixturesByCompetition)
        ->map(function ($competition) use ($data) {
            return [
                'title' => $competition['name'],
                'id' => $competition['uuid'],
                'grouped_by' => $data['group_by'],
                'data' => $this->repository->toCollection(
                    $this->repository
                        ->newQuery()
                        ->whereIn('competition_id', $competition['ids'])
                        ->selectQuery()
                        ->halfRelationQuery()
                        ->sellable()
                        ->withoutSynchronization()
                        ->filter($data)
                        ->limit($data['limit'] ?? 20)
                        ->offset($data['offset'] ?? 0)
                        ->get()
                )->resolve()
            ];
        })
        ->filter(fn($item) => !empty($item['data']))
        ->values()
        ->toArray();
}

补充示例 #

数据表示例 #

Competitions表

Fixtures表

问题示例代码 #

$competitions = Competition::where('synchronized_id', null)
    ->with([
        'fixtures' => fn($q) => $q->orderByDesc('date')->limit(2),
        'synchronizedTo.fixtures' => fn($q) => $q->orderByDesc('date')->limit(2)
    ])->get();

$competitions->map(fn($c) => [
    'fixtures' => $c->fixtures->merge($c->synchronizedTo->fixtures)
]);

该代码合并后的赛程无法按照日期正确排序，且每个赛事分组单独查询赛程会导致效率问题

寻求解决方向 #

• 能否通过Laravel Eloquent的预加载、自定义关联或子查询实现查询优化？
• 使用原生SQL的JOIN/UNION是否更高效，如何与Eloquent框架集成？

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