Spylot Spylot - 19 days ago 5
MySQL Question

Efficient queries to get rows' data, count and total. (atm 32 queries)

I'm using Laravel 5.3 and at the moment I'm building a bill that includes multiple sections. Those sections return data all from 1 table, with the only difference between them is the "type" column which identifies them to be sorted into their appropiate section. For each section, I call 4 queries.

Right now, there are 8 sections. 8 by 4, 32 SQL queries in total.

One section has 4 queries like this:

$records['data'] = Model::where('type', 'x1')->get();
$records['total_cost'] = Model::where('type', 'x1')->sum('cost');
$records['count'] = Model::where('type', 'x1')->count();
$records['total_duration'] = Model::where('type', 'x1')->sum('duration');


And I repeat the same for every section. I don't believe this is the right way.

I'm not sure where to go from here.

Answer

Simply go for this :)

$data = Model::groupBy('type')->get();

$totalCost = $count = $totalDuration = [];

$types = ['x1', 'x2'];

foreach($types as $type) {
  $totalCost[$type] = $data[$type]->sum('cost');
  $count[$type] = $data[$type]->count();
  $totalDuration[$type] = $data[$type]->sum('duration');
}

One query only :)

Comments