Retrieve collection with calculation takes too long to load (Laravel)-CodePudding

I'm working on a collection that needs to calculate some data for each row and it takes too much time to load into view. The problem is I defined an accessor and inside that accessor will perform some calculation and if the data is too big or when user retrieve too many row at once.

Example Model:

public function getCalculationAttribute()
{

    $score_ids = Score::whereIn('id', $this->scores->pluck('score_id'))->pluck('id');
    $count_score = $count_score->count();
    $penalties = Penalty::whereIn('score_id', $score_ids->toArray())->count();
    $balance = $count_score - $penalties;
    $another_score =  $count_score > 0 ? ($balance / $count_score) * 0.7 : 0;

    return [
        'field_a' => $count_score,
        'field_b' => $penalties,
        'field_c' => $balance,
        'field_d' => $another_score
    ];
}

Example Controller

public function index(){
    $data = ExampleModel::get();
    return view('example', ['data' => $data]);
}

Example blade

@foreach($data as $row)
    <p>{{ $row->calculation['field_a']}}</p>
    <p>{{ $row->calculation['field_b']}}</p>
    <p>{{ $row->calculation['field_c']}}</p>
    <p>{{ $row->calculation['field_d']}}</p>
@endforeach

When I didn't need the calculation attribute it works perfectly fine, but when I do and I know each of them will be running query and calculation and it will take forever. Is there any good practice on retrieving data with calculation or any suggestion I can modify this to improve the performance? The code above is just an example. Thank you in advance!

CodePudding user response：

You've got an N 1 query issue with this code. Each time you loop $data and call $row->calculation, you're executing 3 extra queries:

Score::whereIn(...);
$this->scores->pluck('score_id');
...
Penalty::whereIn(...);

You're calling $row->calculation 4 times... I'm pretty sure that means 12 additional queries per row in $data, since get{Whatever}Attribute() doesn't have any kind of caching/logic to know you've called it already.

If you save $row->calculations to a variable, you can reduce that a bit:

@foreach($data as $row)
  @php $calculations = $row->calculations; @endphp
  <p>{{ $calculations['field_a']}}</p>
  <p>{{ $calculations['field_b']}}</p>
  <p>{{ $calculations['field_c']}}</p>
  <p>{{ $calculations['field_d']}}</p>
@endforeach

Additionally, you can eager load the scores relationship to reduce it a bit more:

$data = ExampleModel::with('scores')->get();

Including that will make $this->scores->pluck('score_id'); use the pre-loaded data, and not call an additional query.

Lastly, try to use relationships for your Score::whereIn() and Penalty::whereIn() queries. I'm not sure how you would define them, but if you did, then including those in your ->with() clause will hopefully completely remove this N 1 query issue.