Laravel:如何获得嵌套 hasMany 关系的平均值 (hasManyThrough)

2021-12-26 00:00:00 relationship php mysql laravel eloquent

我有三张桌子:

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

相关文章