query.php

<?php

	/**
	 * 
	 */
	public function get_ratings(Model_User $user)
	{
		$query = $this->reset()->select
		(
			'users.username',
			DB::expr('COALESCE(SUM(ratings_votes.state), 0) AS votes'),
			DB::expr('COALESCE(ratings_votes.state, 0) AS state'),
			DB::expr('(file_ratings.technical + file_ratings.adrenaline + file_ratings.originality) / 3 AS average')
		)
		->join('users')
		->on('file_ratings.user_id', '=', 'users.id')
		->group_by('file_ratings.id');
		
		// If the user is logged in, get their vote per rate
		$query
			->join('ratings_votes', 'LEFT')
			->on('ratings_votes.rating_id', '=', 'file_ratings.id');
		
		if ($user->can('logout'))
		{
			$query->on('ratings_votes.user_id', '=', DB::expr($user->pk()));
		}
		
		return $query;
	}