Laravel: how to get average value for nested relations hasMany (hasManyThrough)

Question

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 overview table is stored in the overview table, the author of the review and has the foreign key product_id. The review_rows table stores ratings for various criteria, such as:

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

Review Lines

linked to the browse table with the foreign key review_id. I established my model relationship as follows:

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

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

I need to summarize and average all review reviews per view, and then summarize and average all of them for each review in order to get an overall rating for this product. Is this possible via Eloquent or do I need another solution or another database design / structure?

Thanks in advance!

Answer 1

- http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/, :

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;
}

:

// 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

Answer 2

, "" php- array_reduce

//model/Reviews.php
public function sum() {
    return array_reduce($this->hasMany('ReviewRows')->lists('rating'), "sumItems");  
}

public function sumItems ($carry, $item) {
    $carry += $item;
    return $carry;
}

Eloquent RAW-, :

//model/Reviews.php
public function avg() {
   $result = $this->hasMany('ReviewRows')
   ->select(DB::raw('avg(rating) average'))
   ->first();
   return $result->average;
}