gistfile1.txt

$poolRanking = $this->find('all', [
            'limit' => $limit,            
            'conditions' => [
                'Pools.tournament_id =' => $tournamentId,
                'Predictions.tournament_id =' => $tournamentId,
            ],
             => [
                [
                    'table' => 'pools_users',
                    'alias' => 'PoolsUsers',
                    'type' => 'LEFT',
                    'conditions'=> ['PoolsUsers.pool_id = Pools.id']
                ],
                [
                    'table' => 'users',
                    'alias' => 'Users',
                    'type' => 'LEFT',
                    'conditions'=> ['Users.id = PoolsUsers.user_id']
                ],
                [
                    'table' => 'predictions',
                    'alias' => 'Predictions',
                    'type' => 'LEFT',
                    'conditions'=> ['Predictions.user_id = Users.id']
                ],
            ],
            'fields' => [
                'Pools.tournament_id',
                'Pools.name',
                'Pools.slug',
                'Predictions.tournament_id',                
                'ROUND(COALESCE((SUM(Predictions.points_score) + SUM(Predictions.points_home_goals) + SUM(Predictions.points_away_goals) + SUM(Predictions.points_toto) + SUM(Predictions.points_diff) + SUM(Predictions.points_home_team) + SUM(Predictions.points_away_team))/COUNT(DISTINCT Predictions.user_id),0),1) AS score',
            ],
            'group' => 'Pools.name',
            'order' => 'score DESC, Pools.name ASC'
        ]);