单个Laravel中的多条件计数选择查询

Question

我正在尝试根据不同的条件来计数票证。我在同一模型上使用了四个不同的查询。我可以仅使用一个查询执行所有计算吗？

$openTickets = Ticket::where('status',1)->count();
$pending = Ticket::where('status',2)->count();
$unAssigned = Ticket::where('agent_id',null)->count();
$unResolved = Ticket::whereNotIn('status',[3,4])->count();

Answer 1

Ticket::selectRaw('COUNT(CASE WHEN status = 1 THEN 1 END) AS open_tickets')
      ->selectRaw('COUNT(CASE WHEN status = 2 THEN 1 END) AS pending_tickets')
      ->selectRaw('COUNT(CASE WHEN agent_id IS NULL THEN 1 END) AS unassigned_tickets')
      ->selectRaw('COUNT(CASE WHEN status NOT IN (3,4) THEN 1 END) AS unresolved_tickets')
      ->first();

您当然可以通过此查询解决多个查询。我们可以使用条件案例和计数。

Answer 2

您可以总结有条件的条件，但在查询中需要许多原始零件：

$result = DB::table('tickets')->whereIn('status', [1,2])->orWhereNull('agent_id')->orWhereNotIn('status', [3,4]) // This is to filter out the things we don't care about
->select([
   DB::raw('SUM(IF(status = 1, 1,0)) as countOpen'),
   DB::raw('SUM(IF(status = 2, 1,0)) as countPending'),
   DB::raw('SUM(IF(agent_id IS NULL, 1,0)) as countUnassigned'),
   DB::raw('SUM(IF(status NOT IN (3,4), 1,0)) as countUnresolved'),
])->first()
$openTickets = $result->countOpen;
$pending = $result->countPending;
$unAssigned = $result->countUnassigned;
$unResolved = $result->countUnresolved;

Answer 3

count（）在语义上适合此任务，而sum（）可以更方便地消耗有条件的表达式，并取决于false/true结果的效果为0或1每个评估。使用selectraw（）一次或四次构建Select子句，然后first（）填充具有所需条件计数的对象。

代码：（ phpize demo ）

var_export(
    DB::table('tickets')
    ->selectRaw("SUM(status = 1) open")
    ->selectRaw("SUM(status = 2) pending")
    ->selectRaw("SUM(agent_id IS NULL) unassigned")
    ->selectRaw("SUM(status NOT IN (3,4)) unresolved")
    ->first()
);

输出：

(object) array(
   'open' => '2',
   'pending' => '3',
   'unassigned' => '2',
   'unresolved' => '6',
)