Laravel中如何从变量和数据库提取特定值统计并生成图表

1. 控制器逻辑调整 #

你需要通过GROUP BY对user_id字段分组，再配合COUNT(*)统计每个取值对应的记录总数，修改后的控制器代码如下：

public function index()
{
    // 原有逻辑保留
    $report = DB::table('transaksis')->whereYear('created_at', date('Y'))
                ->selectRaw("SUM(user_id) AS user_id, SUM(peserta_id) AS peserta_id, SUM(kecamatan_id) AS kecamatan_id")
                ->first();

    $jenis = Jenis::select('id','jenis')->orderBy('id', 'DESC')->take(4)->get();

    $jenispopuler = DB::table('transaksis')
                    ->selectRaw('( SELECT COUNT(jenis_id) FROM transaksis WHERE jenis_id=1  ) AS user_id, ( SELECT COUNT(jenis_id) FROM transaksis WHERE jenis_id=2  ) AS peserta_id, ( SELECT COUNT(jenis_id) FROM transaksis WHERE jenis_id=3  ) AS kecamatan_id')
                    ->groupBy('jenis_id')
                    ->where(\DB::raw('DATE_FORMAT(transaksis.created_at, "%Y")'), '=', date('Y'))
                    ->first();
    
    // 新增：统计user_id不同取值的记录数量
    $userIdStats = DB::table('transaksis')
                    ->whereYear('created_at', date('Y'))
                    ->selectRaw('user_id, COUNT(*) as total')
                    ->groupBy('user_id')
                    // 如果需要限制展示的数量，比如取前10个最多的，就加下面两行
                    // ->orderBy('total', 'DESC')
                    // ->take(10)
                    ->get();

    // 处理成图表需要的格式：x轴的取值、对应的数据值
    $userIds = $userIdStats->pluck('user_id')->toArray();
    $userTotals = $userIdStats->pluck('total')->toArray();

    return view('home', compact('report','jenispopuler','jenis', 'userIds', 'userTotals'));
}

2. 视图层图表配置修改 #

把统计到的数据传入图表的对应配置项即可，示例如下：

// 原有变量判断逻辑保留
...
// 图表配置
xaxis: {
    categories: [@foreach($userIds as $id) '{{$id}}', @endforeach]
},
series: [{
    name: '用户ID对应记录数',
    data: [@foreach($userTotals as $total) {{$total}}, @endforeach]
}]

补充说明 #

• 如果需要单独统计user_id为1和2的总条数，可以直接在控制器加查询：$sumUser12 = DB::table('transaksis')->whereIn('user_id', [1,2])->whereYear('created_at', date('Y'))->count();
• 若user_id存在空值，可以在分组查询时加->whereNotNull('user_id')过滤无效数据

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