Laravel DB Builder复现多关联MySQL查询GROUP_CONCAT结果重复问题

这不是Laravel DB Builder的Bug，问题根源是两个一对多关联同时JOIN会产生笛卡尔积：假设一条主表记录对应3条excuses记录和2条travel记录，JOIN后会生成3×2=6条临时记录，GROUP_CONCAT时就会重复拼接相同内容。

下面给几个解决方法：

方法1：在GROUP_CONCAT中添加DISTINCT #

直接在聚合函数里去重，即使临时表有重复行，也只会保留唯一值：

DB::table('main_table')
    ->leftJoin('excuses', 'main_table.id', '=', 'excuses.main_id')
    ->leftJoin('travel', 'main_table.id', '=', 'travel.main_id')
    ->select(
        'main_table.*',
        DB::raw('GROUP_CONCAT(DISTINCT excuses.content SEPARATOR ", ") as excuses'),
        DB::raw('GROUP_CONCAT(DISTINCT travel.details SEPARATOR ", ") as travel')
    )
    ->groupBy('main_table.id')
    ->get();

方法2：用子查询单独聚合一对多数据 #

避免直接JOIN两个一对多表，通过子查询分别计算每个主记录的聚合结果，从根源避免笛卡尔积：

DB::table('main_table')
    ->select(
        'main_table.*',
        'excuses_agg.excuses',
        'travel_agg.travel'
    )
    ->leftJoin(
        DB::raw('(SELECT main_id, GROUP_CONCAT(content SEPARATOR ", ") as excuses FROM excuses GROUP BY main_id) as excuses_agg'),
        'main_table.id',
        '=',
        'excuses_agg.main_id'
    )
    ->leftJoin(
        DB::raw('(SELECT main_id, GROUP_CONCAT(details SEPARATOR ", ") as travel FROM travel GROUP BY main_id) as travel_agg'),
        'main_table.id',
        '=',
        'travel_agg.main_id'
    )
    ->get();

方法3：使用Eloquent的withAggregate（Laravel 8+） #

如果用Eloquent模型，直接通过关联聚合方法处理，Laravel会自动用子查询避免笛卡尔积：

主模型定义关联：

class MainModel extends Model
{
    public function excuses()
    {
        return $this->hasMany(Excuse::class);
    }

    public function travelRecords()
    {
        return $this->hasMany(Travel::class);
    }
}

查询代码：

MainModel::query()
    ->withAggregate('excuses', 'GROUP_CONCAT(content SEPARATOR ", ")')
    ->withAggregate('travelRecords', 'GROUP_CONCAT(details SEPARATOR ", ")')
    ->get();

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