{
scores->erase(scores->begin(), scores->end());
- pqxx::result res( t.exec(
- "SELECT player,"
- " ("
- " SELECT feetrating"
- " FROM songratings"
- " WHERE machine=( SELECT machine FROM tournaments WHERE tournament=" + pqxx::to_string(tournament) + " ) " // only find songs on the machine we use
- " AND song NOT IN (" // not a song that has been in elimination or seeding
- " SELECT song FROM scores "
- " WHERE tournament=" + pqxx::to_string(tournament) +
- " AND song IS NOT NULL"
- " AND parallel=0"
- " AND chosen='f'"
- " )"
- " AND (player,song) NOT IN (" // not a song the player has chosen before, or is a random song in this round
- " SELECT player,song FROM scores"
- " WHERE tournament=" + pqxx::to_string(tournament) +
- " AND song IS NOT NULL" +
- " AND ( chosen='t' OR round=" + pqxx::to_string(round) + " )"
- " )"
- " AND playmode='single'"
- " ORDER BY feetrating DESC LIMIT 1"
- " ) * 1000 AS max_score FROM tournamentparticipation") );
+ pqxx::result res( t.exec("SELECT * FROM get_max_score_for_players("
+ + pqxx::to_string(tournament) + ", " + pqxx::to_string(round) +
+ ", 'single')") );
for (pqxx::result::const_iterator i = res.begin(); i != res.end(); ++i) {
unsigned player, max_score;
LANGUAGE SQL
STABLE
RETURNS NULL ON NULL INPUT;
+
+-- get_max_score_for_players(tournament, round, playmode)
+CREATE FUNCTION get_max_score_for_players(integer, integer, varchar) RETURNS SETOF max_score
+ AS $$
+ SELECT player,
+ (
+ SELECT feetrating
+ FROM songratings
+ WHERE machine=( -- only find songs on the machine we use
+ SELECT machine FROM tournaments WHERE tournament=$1
+ )
+ AND song NOT IN ( -- not a song that has been in elimination or seeding
+ SELECT song FROM scores
+ WHERE tournament=$1
+ AND song IS NOT NULL
+ AND parallel=0
+ AND chosen='f'
+ )
+ AND (player,song) NOT IN ( -- not a song the player has chosen before, or is a random song in this round
+ SELECT player,song FROM scores
+ WHERE tournament=$1
+ AND song IS NOT NULL
+ AND ( chosen='t' OR round=$2 )
+ )
+ AND playmode=$3
+ ORDER BY feetrating DESC LIMIT 1
+ ) * 1000 AS max_score FROM tournamentparticipation;
+$$
+ LANGUAGE SQL
+ STABLE
+ RETURNS NULL ON NULL INPUT;
+