[SOLVED] Laravel multiple counts with conditions from one table

Laravel multiple counts with conditions from one table

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?

Solution

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();