Laravel 4 - 从模型中返回平均分？

I'd like to return the average score for game reviews in my database. I am building my site with Laravel 4.

我想在我的数据库中返回游戏评论的平均分数。我正在用Laravel 4构建我的网站。

TABLE STRUCTURE:

GAMES (id, title, etc)
REVIEWS (game_id, user_id, score, etc)

CONTROLLER:

public function singleGame($id)
{
    $game = Game::find($id);

    if ($game)
    {
        return View::make('game')->with('game', $game);
    }
    else
    {
        return Redirect::to('/');
    }
}

My thought is to hopefully return the average score for a game through $game->average in my view, but I've been unable to produce the desired result through fiddling with my Game model.

我的想法是希望在我看来通过$ game-> average返回游戏的平均分数,但是我无法通过摆弄我的游戏模型来产生预期的结果。

GAME MODEL:

public function scores()
{
    return $this->hasMany('Review')->avg('score');
}

I've tried a number of methods available to the query builder, but I'm still learning the ropes when it comes to Laravel/PHP, so I'm kinda stuck. Perhaps I should be approaching the problem differently?

我已经尝试了一些查询构建器可用的方法,但是当我谈到Laravel / PHP时,我还在学习绳索,所以我有点卡住了。也许我应该以不同的方式解决问题?

Thanks.

1 个解决方案

#1

Here's two alternatives for you:

这里有两种选择:

Readability (two queries)

可读性(两个查询)

$game = Game::find(1);
$game->average = $game->reviews()->avg('score');

Note that this assumes that you have got a reviews function for your relationship in your game model.

请注意,这假设您的游戏模型中的关系具有评论功能。

public function reviews()
{
    return $this->belongsTo('Game');
}

This alternative is using the avg aggregate function. The aggregate functions provided by the QueryBuilder returns only the aggregate scalar.

这种替代方案是使用avg聚合函数。 QueryBuilder提供的聚合函数仅返回聚合标量。

Performance (one query)

表现(一个查询)

If you really want to do this in one query. Here is one alternative:

如果你真的想在一个查询中这样做。这是一个替代方案:

$game = Game::select('games.*', DB::raw('avg(reviews.score) AS average'))
    ->join('reviews', 'reviews.game_id', '=', 'game.id')
    ->groupBy('reviews.game_id')
    ->where('game.id', '=', 1)
    ->first();

#1