問題描述

我有三張桌子:

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

review 表存儲(chǔ)了評(píng)論文本、評(píng)論作者，并有一個(gè)外部 product_id 鍵.review_rows 表存儲(chǔ)不同標(biāo)準(zhǔn)的評(píng)分，例如:

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

評(píng)論行通過 review_id 外鍵鏈接到評(píng)論表.我已經(jīng)像這樣設(shè)置了我的模型關(guān)系:

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

現(xiàn)在我想在我的類別和產(chǎn)品頁面上顯示產(chǎn)品的平均評(píng)分.我怎樣才能做到這一點(diǎn)?

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

我需要對(duì)每條評(píng)論的所有評(píng)論行求和并求平均值，然后對(duì)每條評(píng)論的所有評(píng)論行求和并求平均值，最終得出該產(chǎn)品的總體評(píng)分.這是否可以通過 Eloquent 實(shí)現(xiàn)，還是我需要不同的解決方案或不同的數(shù)據(jù)庫設(shè)計(jì)/結(jié)構(gòu)?

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/ 僅略微調(diào)整為滿足您的需求:

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

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