[FIXED] Laravel: how to get average on nested hasMany relationships (hasManyThrough)

Issue

I have three tables:

products:   id|name|description|slug|category_id|...
reviews:    id|product_id|review_text|name|email|...
review_rows id|review_id|criteria|rating

the review table stores the review text, writer of the review and has a foreign product_id key. The review_rows table stores the ratings for different criteria like:

----------------------------------------
| id |  criteria  | rating | review_id |
----------------------------------------
|  1 |  price     | 9      | 12        |
----------------------------------------
|  2 |  service   | 8      | 12        |
----------------------------------------
|  3 |  price     | 6      | 54        |
----------------------------------------
|  4 |  service   | 10     | 54        |
----------------------------------------

review rows are linked to the review table with the review_id foreign key. I've set up my model relationships like this:

Product   -> hasMany   -> Review
Review    -> belongsTo -> Product
Review    -> hasMany   -> ReviewRow
ReviewRow -> belongsTo -> Review

Now I would like to display the average rating for a product on my category and product pages. How can I achieve this?

I need to sum and average all the reviewRows per review and then sum and average all of those for each review to end up with the overall rating for that product. Is this possible via Eloquent or do I need a different solution or a different database design/structure?

Thanks in advance!

Solution

You need something like this http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/ only slightly adjusted to match your needs:

public function reviewRows()
{
    return $this->hasManyThrough('ReviewRow', 'Review');
}

public function avgRating()
{
    return $this->reviewRows()
      ->selectRaw('avg(rating) as aggregate, product_id')
      ->groupBy('product_id');
}

public function getAvgRatingAttribute()
{
    if ( ! array_key_exists('avgRating', $this->relations)) {
       $this->load('avgRating');
    }

    $relation = $this->getRelation('avgRating')->first();

    return ($relation) ? $relation->aggregate : null;
}

Then as simple as this:

// eager loading
$products = Product::with('avgRating')->get();
$products->first()->avgRating; // '82.200' | null

// lazy loading via dynamic property
$product = Product::first()
$product->avgRating; // '82.200' | null

Answered By - Jarek Tkaczyk