Laravel 中使用带有条件聚合函数计算总数

假如有电子邮件订阅服务,希望显示订阅者的详情统计页面如下显示

订阅者总数 确认(confirmed) 未经证实(unconfirmed) 取消(cancelled) 拒绝(bounced)
200 150 50 10 5

出于本文的目的,假设我们有一个subscribers包含以下格式数据的数据库表:

name email status
小明 adam@hotmeteor.com confirmed
小红 taylor@laravel.com unconfirmed
小军 jonathan@reinink.ca cancelled
小花 adam.wathan@gmail.com bounced

大部分人的做法:

$total = Subscriber::count();
$confirmed = Subscriber::where('status', 'confirmed')->count();
$unconfirmed = Subscriber::where('status', 'unconfirmed')->count();
$cancelled = Subscriber::where('status', 'cancelled')->count();
$bounced = Subscriber::where('status', 'bounced')->count();

上面这样肯定会产生五条语句,这样做肯定是很不好。所以尝试优化一下,会使用另一个方法解决执行多条语句的问题:

$subscribers = Subscriber::all();
$total = $subscribers->count();
$confirmed = $subscribers->where('status', 'confirmed')->count();
$unconfirmed = $subscribers->where('status', 'unconfirmed')->count();
$cancelled = $subscribers->where('status', 'cancelled')->count();
$bounced = $subscribers->where('status', 'bounced')->count();

上面先获取全部订阅者数据,然后再对这个结果集进行条件统计,使用集合.模型多条数据查询返回Illuminate\Database\Eloquent\Collection这样的方法,只适合再数据量不大的时候使用,如果我们的应用程序有数千或数百万订阅者,处理的时间会很慢,并且会使用大量内存。

条件聚合

实际上有一种非常简单的方法可以查询计算这些总数。诀窍是将条件放在聚合函数中。下面是一个 SQL 示例:

select
  count(*) as total,
  count(case when status = 'confirmed' then 1 end) as confirmed,
  count(case when status = 'unconfirmed' then 1 end) as unconfirmed,
  count(case when status = 'cancelled' then 1 end) as cancelled,
  count(case when status = 'bounced' then 1 end) as bounced
from subscribers

 total | confirmed | unconfirmed | cancelled | bounced
-------+-----------+-------------+-----------+---------
   200 |       150 |          50 |        30 |      25

以下是在 Laravel 中使用查询构建器编写此查询:

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw("count(case when status = 'confirmed' then 1 end) as confirmed")
    ->selectRaw("count(case when status = 'unconfirmed' then 1 end) as unconfirmed")
    ->selectRaw("count(case when status = 'cancelled' then 1 end) as cancelled")
    ->selectRaw("count(case when status = 'bounced' then 1 end) as bounced")
    ->first();

<div>Total: {{ $totals->total }}</div>
<div>Confirmed: {{ $totals->confirmed }}</div>
<div>Unconfirmed: {{ $totals->unconfirmed }}</div>
<div>Cancelled: {{ $totals->cancelled }}</div>
<div>Bounced: {{ $totals->bounced }}</div>

Boolean 列(字段)

表迁移创建 boolean 字段 , model定义属于转换 此处不用model为代码示例,可自行替换为model

如果使用boolean当字段列,将更容易,比如要查询subscribers表中的用户是否为拥有不同的角色权限。假设subscribers表中有is_adminis_treasureris_editoris_manager、字段

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw('count(is_admin or null) as admins')
    ->selectRaw('count(is_treasurer or null) as treasurers')
    ->selectRaw('count(is_editor or null) as editors')
    ->selectRaw('count(is_manager or null) as managers')
    ->first();

这是因为聚合函数count忽略null列。与PHP中false || null返回false不同,在SQL(以及JavaScript)中,它返回null。基本上,如果A可以强制为真,则A || B返回值A;否则,返回B

这段话如果没理解,就看我下面说明:
使用laravel的boolean列,实际数据表里字段为tinyint,值为0(false)1(true), 比如
小明的is_admin字段为1(true),count(is_admin or null)可以看作表示为(1 or null),这A为真 返回A,最终sql为count(is_admin)
反之则是如is_admin字段为0(false),最终sql为count(null),则忽略此列

//PHP  返回 false
var_dump(0 || null) 

//JavaScript 返回 null
console.log(0 || null)

//SQL 返回 null
SELECT (0 or null) as result

翻译原文:本文只是翻译一下大概意思,作为自己单纯记录使用

本作品采用《CC 协议》,转载必须注明作者和本文链接
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
讨论数量: 15
fatrbaby

查询 1 次未必比查询 5 次快。5次 const 级别的查询和一次需要文件排序、零时表、全表扫描的查询你选哪个?

2周前 评论
铁牛 2周前
4pmzzzzzzzzzz (楼主) 2周前
4pmzzzzzzzzzz (楼主) 2周前
fatrbaby (作者) 2周前

这。。。

$subscribers = Subscriber::all();
$total = $subscribers->count();
$confirmed = $subscribers->where('status', 'confirmed')->count();
$unconfirmed = $subscribers->where('status', 'unconfirmed')->count();
$cancelled = $subscribers->where('status', 'cancelled')->count();
$bounced = $subscribers->where('status', 'bounced')->count();
//考虑这样:
$statusTotals =  Subscriber::groupBy('status')
   ->selectRaw('status','count(1) as total')
   ->get()->pluck('total','status');
info(array_sum(array_value($statusTotals)));
info($statusTotals[STATUS_COMFIRMED]);

如果使用 boolean 当字段列,将更容易,比如要查询 subscribers 表中的用户是否为拥有不同的角色权限。假设 subscribers 表中有 is_admin、is_treasurer、is_editor、is_manager、字段

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw('count(is_admin or null) as admins')
    ->selectRaw('count(is_treasurer or null) as treasurers')
    ->selectRaw('count(is_editor or null) as editors')
    ->selectRaw('count(is_manager or null) as managers')
    ->first();

为什么不设置为tinyint当字段列,然后,值为1或0

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw('sum(is_admin) as admins')
    ->selectRaw('sum(is_treasurer) as treasurers')
    ->selectRaw('sum(is_editor) as editors')
    ->selectRaw('sum(is_manager) as managers')
    ->first();
2周前 评论
4pmzzzzzzzzzz (楼主) 2周前
4pmzzzzzzzzzz (楼主) 2周前
____Laravel (作者) 2周前

就没想过 groupBy status 吗

2周前 评论
4pmzzzzzzzzzz (楼主) 2周前
4pmzzzzzzzzzz (楼主) 2周前

count(is_treasurer or null) 这种语句,效率应该更低了吧。原本只需要计数了,现在还多了一步运算。

对于个人而言,如果只是为了临时报表需要这个数据,那我会考虑一条 SQL 查出来 如果是在应用中的我会选择分开 count + 缓存

大部分情况下,status 字段的区分度如果不高的话,实际索引的利用率也不会太高。

2周前 评论

file 平时也挺喜欢这么干的

2周前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!