I have an orders table and need to combine multiple counts depending on specific conditions.
At the moment I have 3 queries :
$orderCount = Order::count(); $newOrderCount = Order::where('status', '=', 'new')->count(); $completedOrderCount = Order::where('status', '=', 'completed')->count(); How can this be combined into 1 query?
Not with eloquent but the first solution that came to my mind
DB::table('orders') ->select(DB::raw('count(*) as order_count')) ->addSelect(DB::raw('sum(case when status = "new" then 1 else 0 end) as new_orders_count')) ->addSelect(DB::raw('sum(case when status = "completed" then 1 else 0 end) as completed_orders_count')) ->get(); Or do it with the collection:
$orders = Order::all(); $orderCount = $orders->count(); $newOrderCount = $orders->where('status', '=', 'new')->count(); $completedOrderCount = $orders->where('status', '=', 'completed')->count(); Use SUM() inside of selectRaw() method calls when you wish to COUNT by a condition -- this offers a concise syntax because it is not necessary to explicitly write CASE or IF expressions.
Code: (PHPize Demo)
var_export( $db::table('orders') ->selectRaw("COUNT(1) count") ->selectRaw("SUM(status = 'new') new") ->selectRaw("SUM(status = 'complete') complete") ->first() ); For the sake of using some fun collection methods, you could call countBy on the status column to create pairs of unique statuses and their counts (there would be no zero counts included), then append the total count. (PHPize Demo)
var_export( $db::table('orders') ->get() ->countBy('status') ->pipe( fn($coll) => $coll->put('count', $coll->sum()) ) ->toArray() ); A long-winded solution, but would do the trick as well.
$orders = Order::all(['id'])->toArray(); $orderCount = 0; $newOrderCount = 0; $completedOrderCount = 0; array_map(function ($o) use (&$orderCount, &$newOrderCount, &$completedOrderCount) { $orderCount++; if($o['status'] === 'new') $newOrderCount++; if($o['status'] === 'completed') $completedOrderCount++; }, $orders); 
$orders.each looks a little non-standard. Is that intended or a typo?
Illuminate\Database\Eloquent\Collection with Illuminate\Support\Collection . Fixed!. Thanks for the correction.👌