From 2278ecd2584050c28e62f0f5fd8967b81d64cc5b Mon Sep 17 00:00:00 2001 From: Ilion Beyst Date: Thu, 13 Oct 2022 17:45:11 +0200 Subject: implement ListBotMatches, allow querying matches by bot/opponent pair --- planetwars-server/src/db/match_queries.rs | 86 +++++++++++++++++++++++++++++++ planetwars-server/src/db/matches.rs | 35 +++++-------- planetwars-server/src/db/mod.rs | 1 + planetwars-server/src/routes/matches.rs | 13 ++++- 4 files changed, 111 insertions(+), 24 deletions(-) create mode 100644 planetwars-server/src/db/match_queries.rs 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, + pub outcome: Option, + + pub before: Option, + pub after: Option, + pub amount: i64, +} + +impl QueryFragment 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::(&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::(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::(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::(after)?; + out.push_sql(" ORDER BY matches.created_at ASC"); + } + out.push_sql(" LIMIT "); + out.push_bind_param::(&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 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, outcome: Option, amount: i64, before: Option, after: Option, conn: &mut PgConnection, ) -> QueryResult> { - 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::(conn)?; + let lbm = ListBotMatches { + bot_id, + opponent_id, + outcome, + before, + after, + amount, + }; + + let matches = lbm.get_results::(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, bot: Option, + opponent: Option, outcome: Option, } @@ -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 { -- cgit v1.2.3