如何在Laravel Eloquent中以子查询按ID分组统计双字段行数

我有如下数据表：

我尝试了以下代码：

$data = UserDocument::select(DB::raw('(SELECT convert_from, convert_to, COUNT(convert_from) FROM user_documents udoc where udoc.user_id = user_id  GROUP BY convert_from, convert_to) as languages'))
                    ->groupby('user_id')
                    ->get(); 

期望得到如下结果：

需要实现：在Laravel Eloquent中通过子查询按user_id分组，统计converted_from和converted_to组合的行数，得到上述期望的统计结果。

方法一：数据库层面聚合+集合分组 #

直接在查询中统计每个用户的语言转换组合数量，再用Laravel集合按user_id分组：

// MySQL版本
$data = UserDocument::select(
    'user_id',
    DB::raw("CONCAT(converted_from, ' -> ', converted_to, ' : ', COUNT(*)) as language_stats")
)
->groupBy('user_id', 'converted_from', 'converted_to')
->get()
->groupBy('user_id');

如果使用PostgreSQL，将CONCAT替换为字符串拼接符||：

$data = UserDocument::select(
    'user_id',
    DB::raw("converted_from || ' -> ' || converted_to || ' : ' || COUNT(*) as language_stats")
)
->groupBy('user_id', 'converted_from', 'converted_to')
->get()
->groupBy('user_id');

渲染视图时可以这样展示成目标表格：

<table>
    <thead>
        <tr>
            <th>user_id</th>
            <th>languages</th>
        </tr>
    </thead>
    <tbody>
        @foreach($data as $userId => $stats)
            @foreach($stats as $index => $stat)
                <tr>
                    <td>{{ $index === 0 ? $userId : '' }}</td>
                    <td>{{ $stat->language_stats }}</td>
                </tr>
            @endforeach
        @endforeach
    </tbody>
</table>

方法二：先查原始统计再格式化 #

先获取基础统计数据，再通过集合方法格式化输出文本：

$data = UserDocument::select(
    'user_id', 
    'converted_from', 
    'converted_to', 
    DB::raw('COUNT(*) as count')
)
->groupBy('user_id', 'converted_from', 'converted_to')
->get()
->groupBy('user_id')
->map(function ($group) {
    return $group->map(function ($item) {
        return "{$item->converted_from} -> {$item->converted_to} : {$item->count}";
    });
});

原代码问题说明 #

你之前的子查询会返回多行结果，但外层查询试图将其作为单个列languages，这会触发数据库报错（子查询返回多行却被当作标量值使用）。正确逻辑是先按user_id+转换组合分组统计，再将同一用户的结果聚合到一起。

内容的提问来源于stack exchange，提问作者S. M. Mahfuzur Rahman