LARAVEL计数与条件

Question

我有一个名为transactions的表，我从中获得数据作为统计数据按年、月和总数分组，但我还需要根据state字段获得success和failed。

问题在于success and field返回total的相同值，如

{
"count": 36,
"success": 36,
"failed": 36,
"date": "01-2022",
"year": 2022,
"month": 1
},

逻辑是

return $this->transactions()
->select(
DB::raw('count(state) as count'),
DB::raw('count( state="success" ) as success'),
DB::raw('count( state!="success" ) as failed'),

DB::raw("DATE_FORMAT(created_at, '%m-%Y') as date"),
DB::raw('YEAR(created_at) year, MONTH(created_at) month')
)
->where(function ($query) use ($start, $end) {
return $query->where('created_at', '>=', $start)
->orWhere('created_at', '>=', $end);
})

->groupBy('year', 'month')
->orderBy('created_at', 'asc')
->get();

Answer 1

你可以这样做:

$this->transactions()
->select(
DB::raw('count(state) as count'),
DB::raw('SUM(case when state = "success" then 1 else 0 end) as success'),
DB::raw('SUM(case when state != "success" then 1 else 0 end) as failed'),
DB::raw("DATE_FORMAT(created_at, '%m-%Y') as date"),
DB::raw('YEAR(created_at) year, MONTH(created_at) month')
)
->where(function ($query) use ($start, $end) {
return $query->where('created_at', '>=', $start)
->orWhere('created_at', '>=', $end);
})

->groupBy('year', 'month')
->orderBy('created_at', 'asc')
->get();

将查询重新格式化为

$this->transactions()
->selectRaw(
'
count(state) as count,
SUM(case when state = "success" then 1 else 0 end) as success,
SUM(case when state != "success" then 1 else 0 end) as failed,
DATE_FORMAT(created_at, "%m-%Y") as date,
YEAR(created_at) year, MONTH(created_at) month
'
)
->where('created_at', '>=', $start)
->orWhere('created_at', '>=', $end)
->groupBy('year', 'month')
->orderBy('created_at')
->get();