問題描述

我有三張桌子:

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

review 表存儲了評論文本、評論作者，并有一個外部 product_id 鍵.review_rows 表存儲不同標準的評分，例如:

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_id 外鍵鏈接到評論表.我已經像這樣設置了我的模型關系:

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?

我需要對每條評論的所有評論行求和并求平均值，然后對每條評論的所有評論行求和并求平均值，最終得出該產品的總體評分.這是否可以通過 Eloquent 實現，還是我需要不同的解決方案或不同的數據庫設計/結構?

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?

提前致謝！

推薦答案

你需要這樣的東西 http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/ 僅略微調整為滿足您的需求:

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

就這么簡單:

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

這篇關于Laravel:如何獲得嵌套 hasMany 關系的平均值 (hasManyThrough)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！