Laravel中按group_id分组统计多type数量并合并查询方法

数据库结构 #

现有代码 #

$stock_get_property = StockPropertyTemplate::groupBy('group_id')
    ->selectRaw('count(type) as quantity , group_id')
    ->where('type' , 'property')
    ->get();

$stock_get_variant = StockPropertyTemplate::groupBy('group_id')
    ->selectRaw('count(type) as quantity , group_id')
    ->where('type' , 'variant')
    ->get();

需求 #

需要合并这两个查询，按group_id分组后同时统计type为property和variant的数量，得到如下格式的结果：

解决方案 #

不用分开查询再合并，直接用条件聚合就能一次搞定，效率还更高，代码如下：

$result = StockPropertyTemplate::groupBy('group_id')
    ->selectRaw('
        group_id,
        COUNT(CASE WHEN type = "property" THEN 1 END) as count_property,
        COUNT(CASE WHEN type = "variant" THEN 1 END) as count_variant
    ')
    ->orderBy('group_id', 'desc') // 可选，让结果和示例排序一致
    ->get();

原理说明 #

• COUNT(CASE WHEN type = "property" THEN 1 END)：当type匹配property时返回1，否则返回NULL，而COUNT函数会自动忽略NULL值，这样就精准统计出了每个分组下property的数量。
• 第二个COUNT逻辑同理，用来统计variant的数量。
• 全程只需要一次数据库查询，比两次查询再手动合并数据的方案性能好很多，数据量大的时候差异更明显。

如果需要给结果加上示例里的自增id字段，可以用集合的map方法处理：

$result = $result->map(function ($item, $index) {
    $item->id = $index + 1;
    return $item;
});

这样处理后，返回的集合就完全符合你想要的格式了！

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