I am trying to count tickets based on different where conditions . I am using four different queries on the same model. Can I execute all of the calculations using just one query?
$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(); 
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(); You can of course resolve the multiple queries with this query. We can use conditional cases and count.
You can sum up conditionals but will need lots of raw parts in your query:
$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; While COUNT() is semantically appropriate for this task, SUM() can more conveniently consume a conditional expression with the effect of summing 0 or 1 depending on the false/true result of each evaluation. Use selectRaw() one or four times to build the SELECT clause, then first() to populate an object with the desired conditional counts.
Code: (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() ); Output:
(object) array( 'open' => '2', 'pending' => '3', 'unassigned' => '2', 'unresolved' => '6', )