Laravel: how to get average on nested hasMany relationships (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 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!

Jarek Tkaczyk · Accepted Answer · 2014-12-30T13:45:45.323

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

Wow, really neat solution! This fits nicely with the Laravel's syntax and ideology! I have one question though. If I run the query, it returns the rating with all the other information on the first review_row, but I would like to only return the avg(rating) value. — Luuk Van Dongen, Dec 30 '14 at 11:45
It returns only `aggregate` and `product_id` - I just edited the answer, previously I forgot the latter. Anyway it shouldn't include anything but the two. — Jarek Tkaczyk, Dec 30 '14 at 13:47

score 2 · Answer 2 · answered Dec 30 '14 at 03:43

Maybe you can try with Eloquent relationships and a little help from php function 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;
}

Or with Eloquent RAW querys like:

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

score 1 · Answer 3 · answered Jan 19 '22 at 13:01

1

Simple and easy solution. Add this into product model

protected $appends = ["avg_rating"];  

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

public function getAvgRatingAttribute()
{
    return round($this->reviewRows->average('rating'),2);
}

answered Jan 19 '22 at 13:01

Arslan Nawaz

41
5

Hi and thanks for the answer. It would be very helpful for everyone of you could append an explanation on how your code works! – Simas Joneliunas Jan 24 '22 at 04:43
This is a bad solution, as it'll get all rows and then make the average. – Simos Fasouliotis Jul 06 '23 at 11:25

score 0 · Answer 4 · answered Mar 04 '21 at 00:42

see https://github.com/faustbrian/laravel-commentable

public function comments(): MorphMany
    {
        return $this->morphMany($this->commentableModel(), 'commentable');
    }

    public function avgRating()
    {
        return $this->comments()->avg("rating");
    }


    $products = \App\Models\Products::with(
        [
            "comments" => function ($q) {
                $q->with(["children" => function ($qch) {
                    $qch->take(2);
                }
                ])->withCount("children")->where("parent_id", '=', null);
            },]
    )->take(5)->get();

    foreach ($products as &$product) {
        $product["avgRating"] = $product->avgRating();
    }

   dd($products);

score 0 · Answer 5 · answered Dec 28 '21 at 10:48

0

use withAvg() as mentioned in laravel official documentation here

answered Dec 28 '21 at 10:48

Prafful Panwar

439
7
19

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

5 Answers5

Linked