Laravel中合并相同zone_id行并汇总关联driver_id的查询方案

我试过StackOverflow上的多种解决方案，但都没用。我有一张名为driver_zones的表，里面存在多条zone_id相同但driver_id不同的记录，需要合并相同zone_id的行，同时关联对应的所有driver_id，求Laravel查询语句。

表结构（前两行zone_id相同）：

期望结果：

Array(
    [0] => Array
        (
            [zone_id] => 6b3ecb1b-c29d-45b2-88b3-dabf5c8b6b62
            [drivers] => Array
                (
                    [0] => Array
                        (
                            [id] => 2
                            [uuid] => sJUfNMZ2nJ
                        )
                    [1] => Array
                        (
                            [uuid] => rz8lpiagH1
                        )
                )
        )
    [1] => Array
        (
            [zone_id] => 5aec1540-b62c-43e3-9182-23a89da610d1
            [drivers] => Array
                (
                    [0] => Array
                        (
                            [uuid] => qY8ynd7tX5
                        )
                )
        )
)

方法一：Eloquent关联（推荐） #

如果有Driver和Zone模型，先在Zone模型里定义关联关系：

// app/Models/Zone.php
public function drivers()
{
    return $this->belongsToMany(Driver::class, 'driver_zones', 'zone_id', 'driver_id');
}

之后直接查询并格式化结果：

$zones = Zone::with('drivers:id,uuid')->get()->map(function ($zone) {
    return [
        'zone_id' => $zone->id, // 若Zone表用uuid存ID，就改成$zone->uuid
        'drivers' => $zone->drivers->toArray()
    ];
});

方法二：直接聚合查询 #

MySQL环境 #

$result = DB::table('driver_zones')
    ->selectRaw('zone_id, GROUP_CONCAT(driver_id) as driver_ids')
    ->groupBy('zone_id')
    ->get()
    ->map(function ($item) {
        $drivers = explode(',', $item->driver_ids);
        return [
            'zone_id' => $item->zone_id,
            'drivers' => array_map(function ($driverId) {
                // 关联Driver表拿详情，不需要的话直接返回['uuid' => $driverId]
                $driver = Driver::where('uuid', $driverId)->select('id', 'uuid')->first();
                return $driver ? $driver->toArray() : ['uuid' => $driverId];
            }, $drivers)
        ];
    });

PostgreSQL环境 #

$result = DB::table('driver_zones')
    ->selectRaw('zone_id, json_agg(driver_id) as driver_ids')
    ->groupBy('zone_id')
    ->get()
    ->map(function ($item) {
        return [
            'zone_id' => $item->zone_id,
            'drivers' => array_map(function ($driverId) {
                $driver = Driver::where('uuid', $driverId)->select('id', 'uuid')->first();
                return $driver ? $driver->toArray() : ['uuid' => $driverId];
            }, $item->driver_ids)
        ];
    });

方法三：集合分组处理 #

先查所有记录，再用Laravel集合分组整理：

$driverZones = DB::table('driver_zones')->get();

$result = $driverZones->groupBy('zone_id')->map(function ($group) {
    return [
        'zone_id' => $group->first()->zone_id,
        'drivers' => $group->map(function ($item) {
            $driver = Driver::where('uuid', $item->driver_id)->select('id', 'uuid')->first();
            return $driver ? $driver->toArray() : ['uuid' => $item->driver_id];
        })->toArray()
    ];
})->values()->toArray();

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