我使用Chart.js来显示每个月的事务总数(count(。
$dataTotal = ModelName::select(DB::raw('count(id) as count'), DB::raw("MONTH(created_at) as month"))
->where('org_id', auth()->user()->org_id)
->where('result_code', 200)
->groupBy('month')
->orderBy('month')
->get()
->toArray();
查询本身很好,只是如果一个月中没有记录,那么它不会返回该月的0
。这会导致图形无法正确渲染。
有人对我如何实现它以便获得完整的结果集有什么建议吗?
所需格式为:
array (
0 =>
array (
'count' => 0,
'month' => 1,
),
1 =>
array (
'count' => 5,
'month' => 2,
),
2 =>
array (
'count' => 0,
'month' => 3,
),
3 =>
array (
'count' => 4,
'month' => 4,
),
4 =>
array (
'count' => 0,
'month' => 5,
),
5 =>
array (
'count' => 4,
'month' => 6,
),
6 =>
array (
'count' => 51225,
'month' => 7,
),
7 =>
array (
'count' => 4,
'month' => 8,
),
8 =>
array (
'count' => 0,
'month' => 9,
),
9 =>
array (
'count' => 0,
'month' => 10,
),
10 =>
array (
'count' => 0,
'month' => 11,
),
11 =>
array (
'count' => 0,
'month' => 12,
),
)
$monthlyArray = array();
$emptyMonth = array('count' => 0, 'month' => 0);
for($i = 1; $i <= 12; $i++){//generate an array with default values
$emptyMonth['month'] = $i;
$monthlyArray[$i-1] = $emptyMonth;
}
$dataTotal = ModelName::select(DB::raw('count(id) as count'), DB::raw("MONTH(created_at) as month"))
->where('org_id', auth()->user()->org_id)
->where('result_code', 200)
->groupBy('month')
->orderBy('month')// you don't really need this one
->get()
->toArray();//fetch the results
foreach($dataTotal as $key => $array){//add the results to the default array
$monthlyArray[$array['month']-1] = $array;
}
//monthlyArray contains the data