From aefa041940daeb6172995ef45507429d11125e90 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Sun, 28 Jan 2024 23:54:10 +0300
Subject: fix: hopefully fix profile placement calculation (#123)

---
 backend/handlers/user.go | 28 ++++++++++++++++------------
 1 file changed, 16 insertions(+), 12 deletions(-)

(limited to 'backend')

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()))
-- 
cgit v1.2.3