fix: hopefully fix profile placement calculation (#123)

1 files changed, 16 insertions, 12 deletions

diff --git a/backend/handlers/user.go b/backend/handlers/user.go
index d550862..88bbe45 100644
--- a/backend/handlers/user.go
+++ b/backend/handlers/user.go
@@ -300,9 +300,10 @@ func Profile(c *gin.Context) {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
-        sql = `WITH map_records AS (SELECT sp.user_id, sp.map_id, 
-        RANK() OVER (PARTITION BY sp.map_id ORDER BY sp.score_count, sp.score_time) AS placement
-        FROM records_sp sp WHERE sp.is_deleted = false) SELECT DISTINCT ON (map_id) placement FROM map_records WHERE user_id = $1`
+        sql = `WITH best_scores AS (SELECT sp.user_id, sp.map_id, MIN(sp.score_count) AS best_score_count, MIN(sp.score_time) AS best_score_time
+        FROM records_sp sp WHERE sp.is_deleted = false GROUP BY sp.user_id, sp.map_id)
+        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement
+        FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement`
         placementsRows, err := database.DB.Query(sql, user.(models.User).SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
@@ -353,9 +354,10 @@ func Profile(c *gin.Context) {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
-        sql = `WITH map_records AS (SELECT mp.host_id, mp.partner_id, mp.map_id, 
-        RANK() OVER (PARTITION BY mp.map_id ORDER BY mp.score_count, mp.score_time) AS placement
-        FROM records_mp mp WHERE mp.is_deleted = false) SELECT DISTINCT ON (map_id) placement FROM map_records WHERE host_id = $1 OR partner_id = $1`
+        sql = `WITH best_scores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, MIN(mp.score_count) AS best_score_count, MIN(mp.score_time) AS best_score_time
+        FROM records_mp mp WHERE mp.is_deleted = false GROUP BY mp.host_id, mp.partner_id, mp.map_id)
+        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement
+        FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement`
         placementsRows, err = database.DB.Query(sql, user.(models.User).SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
@@ -672,9 +674,10 @@ func FetchUser(c *gin.Context) {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
-        sql = `WITH map_records AS (SELECT sp.user_id, sp.map_id, 
-        RANK() OVER (PARTITION BY sp.map_id ORDER BY sp.score_count, sp.score_time) AS placement
-        FROM records_sp sp WHERE sp.is_deleted = false) SELECT DISTINCT ON (map_id) placement FROM map_records WHERE user_id = $1`
+        sql = `WITH best_scores AS (SELECT sp.user_id, sp.map_id, MIN(sp.score_count) AS best_score_count, MIN(sp.score_time) AS best_score_time
+        FROM records_sp sp WHERE sp.is_deleted = false GROUP BY sp.user_id, sp.map_id)
+        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement
+        FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement`
         placementsRows, err := database.DB.Query(sql, user.SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
@@ -725,9 +728,10 @@ func FetchUser(c *gin.Context) {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
-        sql = `WITH map_records AS (SELECT mp.host_id, mp.partner_id, mp.map_id, 
-        RANK() OVER (PARTITION BY mp.map_id ORDER BY mp.score_count, mp.score_time) AS placement
-        FROM records_mp mp WHERE mp.is_deleted = false) SELECT DISTINCT ON (map_id) placement FROM map_records WHERE host_id = $1 OR partner_id = $1`
+        sql = `WITH best_scores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, MIN(mp.score_count) AS best_score_count, MIN(mp.score_time) AS best_score_time
+        FROM records_mp mp WHERE mp.is_deleted = false GROUP BY mp.host_id, mp.partner_id, mp.map_id)
+        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement
+        FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement`
         placementsRows, err = database.DB.Query(sql, user.SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))