Read max score for all players in one shot as well, with a new query and an index...

[ccbs] / bigscreen / fetch_max_score_for_players.cpp

diff --git a/bigscreen/fetch_max_score_for_players.cpp b/bigscreen/fetch_max_score_for_players.cpp
new file mode 100644 (file)
index 0000000..9cc9b2a
--- /dev/null
+++ b/bigscreen/fetch_max_score_for_players.cpp
@@ -0,0 +1,38 @@
+#include "fetch_max_score_for_players.h"
+
+FetchMaxScoreForPlayers::FetchMaxScoreForPlayers(unsigned tournament, unsigned round, std::map<unsigned, unsigned> *scores)
+       : tournament(tournament), round(round), scores(scores) {}
+       
+void FetchMaxScoreForPlayers::operator() (pqxx::transaction<> &t)
+{
+       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 (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) + " )"
+               "       )"
+               "     ORDER BY feetrating DESC LIMIT 1"      
+               "  ) * 1000 AS max_score FROM tournamentparticipation") );
+       
+       for (pqxx::result::const_iterator i = res.begin(); i != res.end(); ++i) {
+               unsigned player, max_score;
+               player = i["player"].as(player);
+               max_score = i["max_score"].as(max_score);
+
+               scores->insert(std::make_pair(player, max_score));
+       }
+}