[SOLVED] Get multiple counts from multiple columns with multiple conditions using just one eloquent query in Laravel

Get multiple counts from multiple columns with multiple conditions using just one eloquent query in Laravel

I need to count several values from the same table. I am currently repeating each query and just modifying the where() clause. This works, but it's hardly using a DRY approach.

This is my current method for calling and returning each required count:

$one = DB::table('stores')->where('store_id', '1')->count();
$two = DB::table('stores')->where('store_id', '2')->count();
$three = DB::table('stores')->where('store_id', '3')->count();
$four = DB::table('stores')->count();
$dutch = DB::table('stores')->where('country', 'Netherlands')->count();
$swedish = DB::table('stores')->where('country', 'Sweden')->count();

return View::make('retailers.index', compact('one','two','three', 'four', 'dutch','swedish'));

My view, I call like so:

{{ $one }}, {{ $two }}, {{ $three }} etc etc

Is there a more suitable approach here then having to make a query for every count to the one table?

Solution

You can use the following query:

$results= DB::table('stores')
             ->select('store_id', DB::raw('count(*) as count'))
             ->groupBy('store_id')
             ->get();

Now in your view, you can loop through it.

@foreach($results as $result)
  {{  $result->store_id  }} 
  {{  $result->count  }}
@endforeach