如何在Laravel+MySQL场景下实现查询结果嵌套子数组的JSON输出

实现方案 #

方案1：Laravel Eloquent关联实现（最推荐，可维护性最高） #

1. 先创建对应数据表的模型，分别为User（对应用户表）和SpecificUserPrice（对应价格独立表）
2. 在app/Models/User.php中定义一对一关联：

public function specificUserPrice()
{
    return $this->hasOne(SpecificUserPrice::class, 'user_id');
}

3. 预加载关联查询并格式化输出：

// 预加载关联避免N+1查询，仅查询需要的字段
$users = User::select('id', 'name')
    ->with('specificUserPrice:user_id,S,M,XL')
    ->get();

// 格式化返回结构
$result = $users->map(function ($user) {
    return [
        'id' => $user->id,
        'name' => $user->name,
        'specific_user_price' => $user->specificUserPrice?->only(['S', 'M', 'XL'])
    ];
});

// 直接返回即可，Laravel会自动转为符合要求的JSON
return $result;

方案2：DB门面原生查询实现（无需定义模型） #

如果不想用Eloquent，可通过两次查询+手动拼装实现，性能同样优异：

// 第一步：查询所有用户数据，以用户ID为键存集合
$users = DB::table('user')->select('id', 'name')->get()->keyBy('id');

// 第二步：批量查询所有对应用户的价格数据，以用户ID为键存集合
$prices = DB::table('specific_user_price')
    ->whereIn('user_id', $users->pluck('id'))
    ->select('user_id', 'S', 'M', 'XL')
    ->get()
    ->keyBy('user_id');

// 第三步：拼装最终结构
$result = $users->map(function ($user) use ($prices) {
    $userPrice = $prices->get($user->id);
    return [
        'id' => $user->id,
        'name' => $user->name,
        'specific_user_price' => $userPrice ? [
            'S' => $userPrice->S,
            'M' => $userPrice->M,
            'XL' => $userPrice->XL
        ] : null
    ];
})->values();

return $result;

方案3：MySQL原生JSON函数实现（直接在数据库层生成结构） #

MySQL 5.7及以上版本支持JSON_OBJECT函数，可直接在查询时生成嵌套JSON结构：

SELECT 
    u.id,
    u.name,
    JSON_OBJECT('S', sup.S, 'M', sup.M, 'XL', sup.XL) AS specific_user_price
FROM user u
LEFT JOIN specific_user_price sup ON u.id = sup.user_id

在Laravel中执行后需要把JSON字符串转为数组：

$users = DB::select($aboveSql);
$result = collect($users)->map(function ($item) {
    $item->specific_user_price = json_decode($item->specific_user_price, true);
    return $item;
});
return $result;

补充说明 #

• 若部分用户无对应价格数据，可自行调整specific_user_price的默认值
• 权限控制可在格式化输出阶段处理，比如根据当前用户权限过滤specific_user_price里的尺码字段，符合前端要求避免返回多余空值

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