Laravel：如何获得平均值嵌套hasMany关系（hasManyThrough） [英] Laravel: how to get average on nested hasMany relationships (hasManyThrough)

问题描述

我有三张表：

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

审查表存储审阅文本，作者的审阅并具有国外的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?

我需要对每个评论的所有reviewRows进行总结和平均，然后对每个评论进行总结和平均，以达到整体评分为该产品。这是否可能通过雄辩，或者我需要一个不同的解决方案或不同的数据库设计/结构？

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）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！