aboutsummaryrefslogtreecommitdiff
path: root/planetwars-server/src/db
diff options
context:
space:
mode:
authorIlion Beyst <ilion.beyst@gmail.com>2022-10-13 17:45:11 +0200
committerIlion Beyst <ilion.beyst@gmail.com>2022-10-13 17:45:11 +0200
commit2278ecd2584050c28e62f0f5fd8967b81d64cc5b (patch)
tree20ec3117ba9a3c65c988d1d044d7539d0ec02a12 /planetwars-server/src/db
parentce07419bbcff91631ade273cbfbed9808b5432e8 (diff)
downloadplanetwars.dev-2278ecd2584050c28e62f0f5fd8967b81d64cc5b.tar.xz
planetwars.dev-2278ecd2584050c28e62f0f5fd8967b81d64cc5b.zip
implement ListBotMatches, allow querying matches by bot/opponent pair
Diffstat (limited to 'planetwars-server/src/db')
-rw-r--r--planetwars-server/src/db/match_queries.rs86
-rw-r--r--planetwars-server/src/db/matches.rs35
-rw-r--r--planetwars-server/src/db/mod.rs1
3 files changed, 99 insertions, 23 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;