From a7d56ba0f54273920743109fe1a6541030f3c003 Mon Sep 17 00:00:00 2001
From: Ilion Beyst <ilion.beyst@gmail.com>
Date: Mon, 9 May 2022 19:41:33 +0200
Subject: add ratings table

---
 planetwars-server/src/db/ratings.rs | 27 +++++++++++++++++++++++++++
 1 file changed, 27 insertions(+)
 create mode 100644 planetwars-server/src/db/ratings.rs

(limited to 'planetwars-server/src/db/ratings.rs')

diff --git a/planetwars-server/src/db/ratings.rs b/planetwars-server/src/db/ratings.rs
new file mode 100644
index 0000000..bee8548
--- /dev/null
+++ b/planetwars-server/src/db/ratings.rs
@@ -0,0 +1,27 @@
+use diesel::{prelude::*, PgConnection, QueryResult};
+use serde::{Deserialize, Serialize};
+
+use crate::schema::{bots, ratings};
+
+#[derive(Queryable, Debug, Insertable, PartialEq, Serialize, Deserialize)]
+pub struct Rating {
+    pub bot_id: i32,
+    pub rating: f64,
+}
+
+pub fn get_rating(bot_id: i32, db_conn: &PgConnection) -> QueryResult<Option<f64>> {
+    ratings::table
+        .filter(ratings::bot_id.eq(bot_id))
+        .select(ratings::rating)
+        .first(db_conn)
+        .optional()
+}
+
+pub fn set_rating(bot_id: i32, rating: f64, db_conn: &PgConnection) -> QueryResult<usize> {
+    diesel::insert_into(ratings::table)
+        .values(Rating { bot_id, rating })
+        .on_conflict(ratings::bot_id)
+        .do_update()
+        .set(ratings::rating.eq(rating))
+        .execute(db_conn)
+}
-- 
cgit v1.2.3


From 30de8107b499741808150db61abecd623bf1581b Mon Sep 17 00:00:00 2001
From: Ilion Beyst <ilion.beyst@gmail.com>
Date: Tue, 17 May 2022 21:13:29 +0200
Subject: implement leaderboard endpoint

---
 planetwars-server/src/db/ratings.rs | 29 ++++++++++++++++++++++++++++-
 1 file changed, 28 insertions(+), 1 deletion(-)

(limited to 'planetwars-server/src/db/ratings.rs')

diff --git a/planetwars-server/src/db/ratings.rs b/planetwars-server/src/db/ratings.rs
index bee8548..8262fed 100644
--- a/planetwars-server/src/db/ratings.rs
+++ b/planetwars-server/src/db/ratings.rs
@@ -1,7 +1,8 @@
 use diesel::{prelude::*, PgConnection, QueryResult};
 use serde::{Deserialize, Serialize};
 
-use crate::schema::{bots, ratings};
+use crate::db::bots::Bot;
+use crate::schema::{bots, ratings, users};
 
 #[derive(Queryable, Debug, Insertable, PartialEq, Serialize, Deserialize)]
 pub struct Rating {
@@ -25,3 +26,29 @@ pub fn set_rating(bot_id: i32, rating: f64, db_conn: &PgConnection) -> QueryResu
         .set(ratings::rating.eq(rating))
         .execute(db_conn)
 }
+
+#[derive(Queryable, Serialize, Deserialize)]
+pub struct Author {
+    id: i32,
+    username: String,
+}
+
+#[derive(Queryable, Serialize, Deserialize)]
+pub struct RankedBot {
+    pub bot: Bot,
+    pub author: Option<Author>,
+    pub rating: f64,
+}
+
+pub fn get_bot_ranking(db_conn: &PgConnection) -> QueryResult<Vec<RankedBot>> {
+    bots::table
+        .left_join(users::table)
+        .inner_join(ratings::table)
+        .select((
+            bots::all_columns,
+            (users::id, users::username).nullable(),
+            ratings::rating,
+        ))
+        .order_by(ratings::rating.desc())
+        .get_results(db_conn)
+}
-- 
cgit v1.2.3