Laravel 4 - Return average score from within model?

Question

I'd like to return the average score for game reviews in my database. I am building my site with 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 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?

Thanks.

Answer 1

Here are 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.

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();