如何在MySQL与Laravel中按分组获取带总数及lead_id集合的结果

背景 #

我们的MySQL数据库中有lead_activity表，包含以下字段：

1. id
2. lead_id
3. activity

示例数据如下：

目前在Laravel中使用如下查询：

$this->data['lead_activities'] = LeadActivity::select(DB::Raw('count(*) as total'), 'activity')->groupBy('activity')->get();

查询结果为：

[
    0 => [
        'total' => 1,
        'activity' => 'Called'
    ],
    1 => [
        'total' => 1,
        'activity' => 'Selled'
    ],
    2 => [
        'total' => 2,
        'activity' => 'Contacted'
    ],
]

需求 #

编写单条查询（Eloquent或原生SQL），直接得到如下格式的结果，无需后续循环处理：

[
    0 => [
        'total' => 1,
        'activity' => 'Called',
        'lead_ids' => [5]
    ],
    1 => [
        'total' => 1,
        'activity' => 'Selled',
        'lead_ids' => [5],
    ],
    2 => [
        'total' => 2,
        'activity' => 'Contacted',
        'lead_ids' => [6,9]
    ],
]

解决方案 #

1. 原生SQL实现

利用MySQL的GROUP_CONCAT函数将同组的lead_id拼接成字符串，后续可转换为数组：

SELECT 
    COUNT(*) AS total,
    activity,
    GROUP_CONCAT(DISTINCT lead_id SEPARATOR ',') AS lead_ids
FROM lead_activity
GROUP BY activity;

2. Laravel Eloquent实现

在查询中调用GROUP_CONCAT，通过集合的map方法将拼接的字符串转为数组：

$this->data['lead_activities'] = LeadActivity::select(
    DB::raw('count(*) as total'),
    'activity',
    DB::raw('GROUP_CONCAT(DISTINCT lead_id SEPARATOR ",") as lead_ids')
)
->groupBy('activity')
->get()
->map(function ($item) {
    $item->lead_ids = explode(',', $item->lead_ids);
    return $item;
})->toArray();

说明：

• 使用DISTINCT避免同一lead_id在同组中重复出现（如果业务允许重复可去掉）
• 若lead_id可能包含逗号，可更换其他分隔符（如|），同步修改GROUP_CONCAT和explode的参数

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