git.sesse.net Git - ccbs/blob - bigscreen/fetch_top_chosen_songs_for_tournament.cpp

   1 #include "fetch_top_chosen_songs_for_tournament.h"
   2
   3 // small utility function so we can stick TopChosenSongs in a std::set
   4 bool TopChosen::operator< (const TopChosen &other) const
   5 {
   6         if (title != other.title)
   7                 return (title < other.title);
   8         return (frequency < other.frequency);
   9 }
  10
  11 FetchTopChosenSongsForTournament::FetchTopChosenSongsForTournament(unsigned tournament, unsigned num, std::vector<TopChosen> *chosen)
  12         : tournament(tournament), num(num), chosen(chosen) {}
  13
  14 void FetchTopChosenSongsForTournament::operator() (pqxx::transaction<> &t)
  15 {
  16         chosen->erase(chosen->begin(), chosen->end());
  17
  18         // Again, this will break if a song has more than one short title
  19         pqxx::result res( t.exec(
  20                 "SELECT title, shorttitle, COUNT(*) AS frequency FROM scores NATURAL JOIN songs NATURAL LEFT JOIN songshorttitles WHERE tournament="
  21                 + pqxx::to_string(tournament) + " AND chosen='t' AND song is NOT NULL GROUP BY title,shorttitle ORDER BY COUNT(*) DESC LIMIT "
  22                 + pqxx::to_string(num)) );
  23
  24         for (pqxx::result::const_iterator i = res.begin(); i != res.end(); ++i) {
  25                 TopChosen ts;
  26
  27                 ts.title = i["title"].as(ts.title);
  28                 ts.shorttitle = i["shorttitle"].as(ts.shorttitle);
  29                 ts.frequency = i["frequency"].as(ts.frequency);
  30
  31                 chosen->push_back(ts);
  32         }
  33 }