如何基于Eloquent查询的分组聚合数据构建多维嵌套JSON结构？

需要生成如下嵌套JSON结构：

[
    {
        "age": 8,
        "countAll": 3,
        "gender": [
            {
                "genderName": "male",
                "countGender": 2
            },
            {
                "genderName": "female",
                "countGender": 1
            }
        ]
    },
    {
        "age": 10,
        "countAll": 1,
        "gender": [
            {
                "genderName": "male",
                "countGender": 0
            },
            {
                "genderName": "female",
                "countGender": 1
            }
        ]
    }
]

现有MySQL表结构及数据：

| id | name     |  gender  | age | user_id | 
--------------------------------------------
| 1  | nameA    | male     | 8   |    1    |
| 2  | nameB    | female   | 10  |    1    |
| 3  | nameC    | male     | 8   |    1    |
| 4  | nameD    | female   | 8   |    1    |

使用Laravel 10框架，当前编写的Eloquent查询及处理代码：

$test = Participant::select('age', DB::raw('COUNT(age) as countAge'), DB::raw('COUNT(gender) as  countGender'))
    ->where('user_id', $user_id)
    ->groupBy('age')
    ->orderBy('age', 'ASC')
    ->get();

$output = array();
$currentAge = "";
$currentCount = "";

foreach ($test as $data) {
   if ($data->age != $currentAge) {
      $output[] = array();

      end($output);
      $currentItem = &$output[key($output)];
      $currentAge = $data->age;
      $currentCount = $data->countAge;
      $currentItem['age'] = $currentAge;
      $currentItem['countAll'] = $currentCount;
      $currentItem['gender'] = array();
    }
    $currentItem['gender'][] = array('genderName' => $data->gender, 'countGender' => $data->countGender);
}

但JSON编码后genderName字段为空，且未按预期拆分性别统计，请问如何修改Eloquent查询以生成符合要求的嵌套JSON结构？

1. 核心问题分析 #

原查询仅按age分组，无法获取每个年龄下不同性别的细分数据，同时未正确关联gender字段到分组逻辑，导致后续处理时genderName无有效值。

2. 方案一：两次查询+数据重组 #

先分别获取年龄总人数和各年龄性别细分统计，再合并生成目标结构：

// 1. 获取每个年龄下的性别统计数据
$genderStats = Participant::select(
    'age',
    'gender',
    DB::raw('COUNT(*) as countGender')
)
->where('user_id', $user_id)
->groupBy('age', 'gender')
->orderBy('age', 'ASC')
->get();

// 2. 获取每个年龄的总人数
$ageTotals = Participant::select(
    'age',
    DB::raw('COUNT(*) as countAll')
)
->where('user_id', $user_id)
->groupBy('age')
->pluck('countAll', 'age');

// 3. 重组数据生成目标结构
$output = [];
$targetGenders = ['male', 'female']; // 定义需要统计的性别列表

// 按年龄分组整理性别统计
$groupedByAge = $genderStats->groupBy('age');

foreach ($groupedByAge as $age => $stats) {
    // 初始化性别统计，默认计数为0
    $genderCounts = collect($targetGenders)->mapWithKeys(function ($gender) {
        return [$gender => 0];
    });

    // 填充实际统计的性别数量
    foreach ($stats as $stat) {
        $genderCounts[$stat->gender] = $stat->countGender;
    }

    // 组装当前年龄的最终结构
    $output[] = [
        'age' => (int)$age,
        'countAll' => $ageTotals[$age] ?? 0,
        'gender' => $genderCounts->map(function ($count, $gender) {
            return [
                'genderName' => $gender,
                'countGender' => $count
            ];
        })->values()->toArray()
    ];
}

// 转为JSON
$jsonResult = json_encode($output, JSON_PRETTY_PRINT);

3. 方案二：单查询+CASE统计（更高效） #

用SQL的CASE语句直接在查询中统计各性别数量，避免两次查询：

$stats = Participant::select(
    'age',
    DB::raw('COUNT(*) as countAll'),
    DB::raw('SUM(CASE WHEN gender = "male" THEN 1 ELSE 0 END) as male_count'),
    DB::raw('SUM(CASE WHEN gender = "female" THEN 1 ELSE 0 END) as female_count')
)
->where('user_id', $user_id)
->groupBy('age')
->orderBy('age', 'ASC')
->get();

// 直接重组为目标结构
$output = $stats->map(function ($item) {
    return [
        'age' => $item->age,
        'countAll' => $item->countAll,
        'gender' => [
            ['genderName' => 'male', 'countGender' => $item->male_count],
            ['genderName' => 'female', 'countGender' => $item->female_count]
        ]
    ];
})->toArray();

$jsonResult = json_encode($output, JSON_PRETTY_PRINT);

内容的提问来源于stack exchange，提问作者Pettonk Kacoa'