php - 获取 Laravel GroupBy 的平均计数

Question

我正在尝试制作评论系统，我想在使用分组依据时获得计数(id)的平均值

如果我将 count 与 group by 结合使用，它会起作用并且我得到

return $comments  = Comment::select('rating',DB::raw('count(id)  as 
total_reviews'))->where('product_id', $id)->where('shop_name', $shop)-
>groupBy('rating')->get();

结果

  [
{
"rating": 1,
"total_reviews": 1
},
{
"rating": 2,
"total_reviews": 2
},
{
"rating": 3,
"total_reviews": 2
},
{
"rating": 4,
"total_reviews": 6
},
{
"rating": 5,
"total_reviews": 61
}
]

但是当我添加 avg() 时出现错误

return $comments = Comment::select('rating',DB::raw('avg(count(id))
as total_reviews'))->where('product_id', $id)->where('shop_name', $shop)-
>groupBy('rating')->get();

General error: 1111 Invalid use of group function (SQL: select rating, avg(count(id)) as total_reviews from comments.....

Answer 1

Laravel Documentation 中有一个很好的例子它做了类似的事情，检查一下:

$users = DB::table('users')
                     ->select(DB::raw('count(*) as user_count, status'))
                     ->where('status', '<>', 1)
                     ->groupBy('status')
                     ->get();

使用数据库外观尝试这种方式，并告诉我发生了什么。