diff options
author | Ilion Beyst <ilion.beyst@gmail.com> | 2022-10-13 17:45:11 +0200 |
---|---|---|
committer | Ilion Beyst <ilion.beyst@gmail.com> | 2022-10-13 17:45:11 +0200 |
commit | 2278ecd2584050c28e62f0f5fd8967b81d64cc5b (patch) | |
tree | 20ec3117ba9a3c65c988d1d044d7539d0ec02a12 /planetwars-server | |
parent | ce07419bbcff91631ade273cbfbed9808b5432e8 (diff) | |
download | planetwars.dev-2278ecd2584050c28e62f0f5fd8967b81d64cc5b.tar.xz planetwars.dev-2278ecd2584050c28e62f0f5fd8967b81d64cc5b.zip |
implement ListBotMatches, allow querying matches by bot/opponent pair
Diffstat (limited to 'planetwars-server')
-rw-r--r-- | planetwars-server/src/db/match_queries.rs | 86 | ||||
-rw-r--r-- | planetwars-server/src/db/matches.rs | 35 | ||||
-rw-r--r-- | planetwars-server/src/db/mod.rs | 1 | ||||
-rw-r--r-- | planetwars-server/src/routes/matches.rs | 13 |
4 files changed, 111 insertions, 24 deletions
diff --git a/planetwars-server/src/db/match_queries.rs b/planetwars-server/src/db/match_queries.rs new file mode 100644 index 0000000..2e89706 --- /dev/null +++ b/planetwars-server/src/db/match_queries.rs @@ -0,0 +1,86 @@ +use super::matches::BotMatchOutcome; +use crate::schema::matches; +use chrono::NaiveDateTime; +use diesel::pg::Pg; +use diesel::query_builder::{AstPass, Query, QueryFragment, QueryId}; +use diesel::sql_types::*; +use diesel::{PgConnection, QueryResult, RunQueryDsl}; + +pub struct ListBotMatches { + pub bot_id: i32, + pub opponent_id: Option<i32>, + pub outcome: Option<BotMatchOutcome>, + + pub before: Option<NaiveDateTime>, + pub after: Option<NaiveDateTime>, + pub amount: i64, +} + +impl QueryFragment<Pg> for ListBotMatches { + fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, Pg>) -> QueryResult<()> { + out.unsafe_to_cache_prepared(); + + out.push_sql("SELECT matches.* FROM matches"); + out.push_sql(" JOIN ("); + out.push_sql(concat!( + "SELECT match_id, player_id, bot_version_id, bot_id ", + "FROM match_players ", + "JOIN bot_versions ON match_players.bot_version_id = bot_versions.id ", + "WHERE bot_id = " + )); + out.push_bind_param::<Integer, _>(&self.bot_id)?; + out.push_sql(") main_player ON matches.id = main_player.match_id"); + + if let Some(opponent_id) = self.opponent_id.as_ref() { + out.push_sql(" JOIN ("); + out.push_sql(concat!( + "SELECT match_id, player_id, bot_version_id, bot_id ", + "FROM match_players ", + "JOIN bot_versions ON match_players.bot_version_id = bot_versions.id ", + "WHERE bot_id = " + )); + out.push_bind_param::<Integer, _>(opponent_id)?; + out.push_sql(") other_player ON matches.id = other_player.match_id"); + } + + out.push_sql(" WHERE matches.state = 'finished' AND matches.is_public = true"); + if let Some(outcome) = self.outcome.as_ref() { + match outcome { + BotMatchOutcome::Win => { + out.push_sql(" AND matches.winner = main_player.player_id"); + } + BotMatchOutcome::Loss => { + out.push_sql(" AND matches.winner <> main_player.player_id"); + } + BotMatchOutcome::Tie => { + out.push_sql(" AND matches.winner IS NULL"); + } + } + } + if let Some(before) = self.before.as_ref() { + out.push_sql(" AND matches.created_at < "); + out.push_bind_param::<Timestamp, _>(before)?; + out.push_sql(" ORDER BY matches.created_at DESC"); + } else if let Some(after) = self.after.as_ref() { + out.push_sql(" AND matches.created_at > "); + out.push_bind_param::<Timestamp, _>(after)?; + out.push_sql(" ORDER BY matches.created_at ASC"); + } + out.push_sql(" LIMIT "); + out.push_bind_param::<BigInt, _>(&self.amount)?; + + Ok(()) + } +} + +impl Query for ListBotMatches { + type SqlType = matches::SqlType; +} + +impl QueryId for ListBotMatches { + type QueryId = (); + + const HAS_STATIC_QUERY_ID: bool = false; +} + +impl RunQueryDsl<PgConnection> for ListBotMatches {} diff --git a/planetwars-server/src/db/matches.rs b/planetwars-server/src/db/matches.rs index 813f998..d628b14 100644 --- a/planetwars-server/src/db/matches.rs +++ b/planetwars-server/src/db/matches.rs @@ -14,6 +14,7 @@ use crate::schema::{bot_versions, bots, maps, match_players, matches}; use super::bots::{Bot, BotVersion}; use super::maps::Map; +use super::match_queries::ListBotMatches; #[derive(Insertable)] #[diesel(table_name = matches)] @@ -173,35 +174,23 @@ pub fn list_public_matches( pub fn list_bot_matches( bot_id: i32, + opponent_id: Option<i32>, outcome: Option<BotMatchOutcome>, amount: i64, before: Option<NaiveDateTime>, after: Option<NaiveDateTime>, conn: &mut PgConnection, ) -> QueryResult<Vec<FullMatchData>> { - let mut query = finished_public_matches_query(before, after) - .inner_join(match_players::table) - .inner_join( - bot_versions::table.on(match_players::bot_version_id.eq(bot_versions::id.nullable())), - ) - .filter(bot_versions::bot_id.eq(bot_id)) - .select(matches::all_columns); - - if let Some(outcome) = outcome { - query = match outcome { - BotMatchOutcome::Win => { - query.filter(matches::winner.eq(match_players::player_id.nullable())) - } - BotMatchOutcome::Loss => { - query.filter(matches::winner.ne(match_players::player_id.nullable())) - } - BotMatchOutcome::Tie => query.filter(matches::winner.is_null()), - }; - } - - query = query.limit(amount); - - let matches = query.get_results::<MatchBase>(conn)?; + let lbm = ListBotMatches { + bot_id, + opponent_id, + outcome, + before, + after, + amount, + }; + + let matches = lbm.get_results::<MatchBase>(conn)?; fetch_full_match_data(matches, conn) } diff --git a/planetwars-server/src/db/mod.rs b/planetwars-server/src/db/mod.rs index f014cea..ec6f94e 100644 --- a/planetwars-server/src/db/mod.rs +++ b/planetwars-server/src/db/mod.rs @@ -1,5 +1,6 @@ pub mod bots; pub mod maps; +pub mod match_queries; pub mod matches; pub mod ratings; pub mod sessions; diff --git a/planetwars-server/src/routes/matches.rs b/planetwars-server/src/routes/matches.rs index 3ad10cf..99c6d1a 100644 --- a/planetwars-server/src/routes/matches.rs +++ b/planetwars-server/src/routes/matches.rs @@ -42,6 +42,7 @@ pub struct ListRecentMatchesParams { after: Option<NaiveDateTime>, bot: Option<String>, + opponent: Option<String>, outcome: Option<BotMatchOutcome>, } @@ -70,8 +71,18 @@ pub async fn list_recent_matches( Some(bot_name) => { let bot = db::bots::find_bot_by_name(&bot_name, &mut conn) .map_err(|_| StatusCode::BAD_REQUEST)?; + + let opponent_id = if let Some(opponent_name) = params.opponent { + let opponent = db::bots::find_bot_by_name(&opponent_name, &mut conn) + .map_err(|_| StatusCode::BAD_REQUEST)?; + Some(opponent.id) + } else { + None + }; + matches::list_bot_matches( bot.id, + opponent_id, params.outcome, count, params.before, @@ -82,7 +93,7 @@ pub async fn list_recent_matches( None => matches::list_public_matches(count, params.before, params.after, &mut conn), }; - let mut matches = matches_result.map_err(|_| StatusCode::BAD_REQUEST)?; + let mut matches = matches_result.expect("failed to get matches"); //.map_err(|_| StatusCode::BAD_REQUEST)?; let mut has_next = false; if matches.len() > requested_count { |