Laravel：如何获得嵌套 hasMany 关系的平均值 (hasManyThrough)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27698690/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel: how to get average on nested hasMany relationships (hasManyThrough)
提问by Luuk Van Dongen
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:
评论表存储评论文本、评论作者并有一个外部 product_id 键。review_rows 表存储不同标准的评分，例如：
----------------------------------------
| 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:
评论行通过 review_id 外键链接到评论表。我已经建立了这样的模型关系：
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?
我需要对每个评论的所有 reviewRows 求和并求平均值，然后对每个评论的所有这些求和求平均值，最终得出该产品的总体评分。这是否可以通过 Eloquent 实现，或者我是否需要不同的解决方案或不同的数据库设计/结构？
Thanks in advance!
提前致谢！
回答by Jarek Tkaczyk
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:
你需要这样的东西http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/只是稍微调整以满足您的需求：
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
回答by Edgar Orozco
Maybe you can try with Eloquent relationships and a little help from php function array_reduce
也许您可以尝试使用 Eloquent 关系以及 php 函数 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:
或者使用 Eloquent RAW 查询，例如：
//model/Reviews.php
public function avg() {
$result = $this->hasMany('ReviewRows')
->select(DB::raw('avg(rating) average'))
->first();
return $result->average;
}
