Question 215589 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Eloquent: get average score by foreign model

I have three models:

  • Product
  • Review
  • Score

They are linked like this:

  • Product (hasMany) -> Review (hasOne) -> Score

Review model:

  • text
  • product_id
  • score_id

Score model:

  • value (number)

My relation in Product model:

public function reviews () {
  return $this->hasMany(Review::class, 'product_id', 'id');
}

public function averageScore () {
  // ???
}

I need to calculate average product score (by score.value prop), but have no idea how to achieve it.

Tags:

2

Answers


  1. 0

    Two routes you can go the pure SQL approach or you can use Eloquent to eager load it and calculate it. The last solution avoids N+1 problems the best but sometimes SQL only solutions can be beneficial, that you don’t have to iterate over as many models.

    Eloquent solution

    // when fetching products
Product::with('reviews.score');


// In your product model
public function averageScore () {
    return $this->reviews->sum(function (Review $review) {
        $review->score->value;
    });
}

    Here you have to iterate over most of the relationships, but everything is eager loaded. Doing this for multiple will produce minimal SQL queries.

    SQL solution

    public function averageScore () {
    return Score::whereIn('id', $this->reviews()->pluck('score_id'))
        ->sum('value');
}

    If you iterate over this, it will create N queries. Is pretty efficient when only called once.

    Login or Signup to reply.
  2. 0

    You can do this function by collection or by the query builder both

    The collection approach is

    public function averageScore(){
$reviews = $this->reviews;

$averageScore = $reviews->map(function ($review) {
    return $review->score->value;
})->avg();
return $averageScore ?? 0;
}

    The query bilder approach is below

    public function averageScore(){
$reviews = $this->reviews;

$averageScore = Review::where('product_id', $this->id)
    ->join('scores', 'reviews.score_id', '=', 'scores.id')
    ->avg('scores.value');
});
return $averageScore ?? 0;
}

    However the query builder approach is more optimized. You may use one as per your use case.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search