aboutsummaryrefslogtreecommitdiff
path: root/backend/handlers
diff options
context:
space:
mode:
authorArda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>2024-10-19 16:28:00 +0300
committerArda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>2024-10-19 16:28:00 +0300
commitbe5313fa0092688cea04f7f1f115574765847c22 (patch)
treed3760749ffb6942577afe92182f6fdbd3aa89d4b /backend/handlers
parentbackend: optimize create record (diff)
downloadlphub-be5313fa0092688cea04f7f1f115574765847c22.tar.gz
lphub-be5313fa0092688cea04f7f1f115574765847c22.tar.bz2
lphub-be5313fa0092688cea04f7f1f115574765847c22.zip
backend: fix run ranks
Diffstat (limited to 'backend/handlers')
-rw-r--r--backend/handlers/user.go40
1 files changed, 24 insertions, 16 deletions
diff --git a/backend/handlers/user.go b/backend/handlers/user.go
index 908063f..17a7819 100644
--- a/backend/handlers/user.go
+++ b/backend/handlers/user.go
@@ -301,10 +301,12 @@ func Profile(c *gin.Context) {
301 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 301 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
302 return 302 return
303 } 303 }
304 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 304 sql = `WITH best_scores AS (WITH RankedScores AS (SELECT sp.user_id, sp.map_id, sp.score_count, sp.score_time, ROW_NUMBER() OVER (
305 FROM records_sp sp WHERE sp.is_deleted = false GROUP BY sp.user_id, sp.map_id) 305 PARTITION BY sp.user_id, sp.map_id ORDER BY sp.score_count ASC, sp.score_time ASC) AS rank FROM records_sp sp WHERE sp.is_deleted = false)
306 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 306 SELECT user_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
307 FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement` 307 SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id
308 AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count
309 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`
308 placementsRows, err := database.DB.Query(sql, user.(models.User).SteamID) 310 placementsRows, err := database.DB.Query(sql, user.(models.User).SteamID)
309 if err != nil { 311 if err != nil {
310 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 312 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
@@ -355,10 +357,12 @@ func Profile(c *gin.Context) {
355 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 357 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
356 return 358 return
357 } 359 }
358 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 360 sql = `WITH best_scores AS (WITH RankedScores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, mp.score_count, mp.score_time, ROW_NUMBER() OVER (
359 FROM records_mp mp WHERE mp.is_deleted = false GROUP BY mp.host_id, mp.partner_id, mp.map_id) 361 PARTITION BY mp.host_id, mp.partner_id, mp.map_id ORDER BY mp.score_count ASC, mp.score_time ASC) AS rank FROM records_mp mp WHERE mp.is_deleted = false)
360 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 362 SELECT host_id, partner_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
361 FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement` 363 SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id
364 AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count
365 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`
362 placementsRows, err = database.DB.Query(sql, user.(models.User).SteamID) 366 placementsRows, err = database.DB.Query(sql, user.(models.User).SteamID)
363 if err != nil { 367 if err != nil {
364 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 368 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
@@ -675,10 +679,12 @@ func FetchUser(c *gin.Context) {
675 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 679 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
676 return 680 return
677 } 681 }
678 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 682 sql = `WITH best_scores AS (WITH RankedScores AS (SELECT sp.user_id, sp.map_id, sp.score_count, sp.score_time, ROW_NUMBER() OVER (
679 FROM records_sp sp WHERE sp.is_deleted = false GROUP BY sp.user_id, sp.map_id) 683 PARTITION BY sp.user_id, sp.map_id ORDER BY sp.score_count ASC, sp.score_time ASC) AS rank FROM records_sp sp WHERE sp.is_deleted = false)
680 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 684 SELECT user_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
681 FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement` 685 SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id
686 AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count
687 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`
682 placementsRows, err := database.DB.Query(sql, user.SteamID) 688 placementsRows, err := database.DB.Query(sql, user.SteamID)
683 if err != nil { 689 if err != nil {
684 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 690 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
@@ -729,10 +735,12 @@ func FetchUser(c *gin.Context) {
729 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 735 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
730 return 736 return
731 } 737 }
732 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 738 sql = `WITH best_scores AS (WITH RankedScores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, mp.score_count, mp.score_time, ROW_NUMBER() OVER (
733 FROM records_mp mp WHERE mp.is_deleted = false GROUP BY mp.host_id, mp.partner_id, mp.map_id) 739 PARTITION BY mp.host_id, mp.partner_id, mp.map_id ORDER BY mp.score_count ASC, mp.score_time ASC) AS rank FROM records_mp mp WHERE mp.is_deleted = false)
734 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 740 SELECT host_id, partner_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
735 FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement` 741 SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id
742 AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count
743 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`
736 placementsRows, err = database.DB.Query(sql, user.SteamID) 744 placementsRows, err = database.DB.Query(sql, user.SteamID)
737 if err != nil { 745 if err != nil {
738 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 746 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
generated by cgit v1.2.3 (git 2.39.1) at 2025-11-08 21:50:18 +0000