Laravel5.7 多表关联查询按用户分组嵌套分类数据问题

• 初始代码中DB::table('users','category')写法错误：table()方法仅接收1个主表名参数，多余参数不会被识别为关联表，你当前未触发报错是因为第二个参数被直接忽略，category表实际是通过后续leftJoin引入的。
• 链式调用末尾的->groupBy('user_id')调用的是Laravel集合的分组方法，而非SQL层面的分组：该方法只会将同user_id的记录归到对应分组键下，不会自动合并分类字段、也不会去除重复的用户基础字段。
• 若直接在查询构造器中调用SQL层面的groupBy('user_id')，在非严格模式下仅会返回该用户关联的第一条分类数据，SQL原生分组逻辑会将同组多行记录压缩为单条，无法自动将多行分类值组装为数组结构。
• distinct()方法仅能保证整行返回结果不重复，无法解决一对多关联查询必然带来的主表（users表）字段重复问题。

优先推荐集合重组方案，无数据库兼容问题、不会出现长度截断，完全兼容Laravel 5.7版本：

// 第一步：查询所有扁平化关联数据，移除错误的table多参数、移除末尾的集合groupBy
$rawData = DB::table('users')
    ->leftJoin('service_provider_rating', 'users.id', '=', 'service_provider_rating.rating_receiver_user_id')
    ->leftJoin('service_provider_category', 'users.id', '=', 'service_provider_category.user_id')
    ->leftJoin('category', 'category.id', '=', 'service_provider_category.category_id')
    ->select('users.id as user_id','users.full_name','category.id as category_id','category.title')
    ->distinct()
    ->get();

// 第二步：通过集合方法重组为目标结构
$nearProviderdata = $rawData->groupBy('user_id')->map(function ($userRecords) {
    // 取分组内第一条记录的用户基础信息，避免字段重复
    $userBase = $userRecords->first();
    // 提取所有关联分类，过滤左连接产生的空分类记录
    $categoryList = $userRecords->filter(function ($record) {
        return !is_null($record->category_id);
    })->map(function ($record) {
        return [
            'category_id' => $record->category_id,
            'title' => $record->title
        ];
    })->values();

    return (object)[
        'user_id' => $userBase->user_id,
        'full_name' => $userBase->full_name,
        'category' => $categoryList
    ];
})->values();

方案注意事项 #

• 如果需要统计服务商评分，必须在select中使用聚合函数（如DB::raw('AVG(service_provider_rating.score) as avg_rating')），否则多表关联会产生笛卡尔积，导致评分计算结果翻倍。
• 无关联分类的用户，返回结构中category字段为空数组，符合常规接口返回逻辑。

如果希望减少PHP层处理逻辑，也可以使用MySQL的GROUP_CONCAT函数在SQL层先做聚合，适合单用户关联分类数量较少的场景：

$nearProviderdata = DB::table('users')
    ->leftJoin('service_provider_rating', 'users.id', '=', 'service_provider_rating.rating_receiver_user_id')
    ->leftJoin('service_provider_category', 'users.id', '=', 'service_provider_category.user_id')
    ->leftJoin('category', 'category.id', '=', 'service_provider_category.category_id')
    ->select(
        'users.id as user_id',
        'users.full_name',
        // 用特殊分隔符拼接分类ID和名称，注意分隔符不要和分类标题内容冲突
        DB::raw('GROUP_CONCAT(DISTINCT category.id, "::", category.title SEPARATOR "||") as category_raw')
    )
    ->groupBy('users.id', 'users.full_name')
    ->get()
    ->map(function ($item) {
        $category = [];
        if ($item->category_raw) {
            foreach (explode('||', $item->category_raw) as $catItem) {
                [$catId, $catTitle] = explode('::', $catItem);
                $category[] = [
                    'category_id' => $catId,
                    'title' => $catTitle
                ];
            }
        }
        $item->category = $category;
        unset($item->category_raw);
        return $item;
    });

该方案存在已知限制：MySQL默认GROUP_CONCAT长度限制为1024字符，单用户关联分类过多时会出现内容截断，非特殊场景不推荐使用。

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