From be5313fa0092688cea04f7f1f115574765847c22 Mon Sep 17 00:00:00 2001 From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> Date: Sat, 19 Oct 2024 16:28:00 +0300 Subject: backend: fix run ranks --- backend/go.mod | 1 + backend/go.sum | 2 ++ backend/handlers/user.go | 40 ++++++++++++++++++++++++---------------- backend/main.go | 28 +++++++++++++++------------- 4 files changed, 42 insertions(+), 29 deletions(-) (limited to 'backend') diff --git a/backend/go.mod b/backend/go.mod index f6eef48..ae50685 100644 --- a/backend/go.mod +++ b/backend/go.mod @@ -8,6 +8,7 @@ require ( ) require ( + github.com/gin-contrib/cors v1.7.2 github.com/golang-jwt/jwt/v4 v4.5.0 github.com/google/uuid v1.6.0 github.com/pektezol/steam_go v1.1.2 diff --git a/backend/go.sum b/backend/go.sum index 10504e4..f117b31 100644 --- a/backend/go.sum +++ b/backend/go.sum @@ -31,6 +31,8 @@ github.com/felixge/httpsnoop v1.0.4 h1:NFTV2Zj1bL4mc9sqWACXbQFVBBg2W3GPvqp8/ESS2 github.com/felixge/httpsnoop v1.0.4/go.mod h1:m8KPJKqk1gH5J9DgRY2ASl2lWCfGKXixSwevea8zH2U= github.com/gabriel-vasile/mimetype v1.4.5 h1:J7wGKdGu33ocBOhGy0z653k/lFKLFDPJMG8Gql0kxn4= github.com/gabriel-vasile/mimetype v1.4.5/go.mod h1:ibHel+/kbxn9x2407k1izTA1S81ku1z/DlgOW2QE0M4= +github.com/gin-contrib/cors v1.7.2 h1:oLDHxdg8W/XDoN/8zamqk/Drgt4oVZDvaV0YmvVICQw= +github.com/gin-contrib/cors v1.7.2/go.mod h1:SUJVARKgQ40dmrzgXEVxj2m7Ig1v1qIboQkPDTQ9t2E= github.com/gin-contrib/gzip v0.0.6 h1:NjcunTcGAj5CO1gn4N8jHOSIeRFHIbn51z6K+xaN4d4= github.com/gin-contrib/gzip v0.0.6/go.mod h1:QOJlmV2xmayAjkNS2Y8NQsMneuRShOU/kjovCXNuzzk= github.com/gin-contrib/sse v0.1.0 h1:Y/yl/+YNO8GZSjAhjMsSuLt29uWRFHdHYUb5lYOV9qE= 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) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - 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` + sql = `WITH best_scores AS (WITH RankedScores AS (SELECT sp.user_id, sp.map_id, sp.score_count, sp.score_time, ROW_NUMBER() OVER ( + 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) + SELECT user_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1) + 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())) @@ -355,10 +357,12 @@ func Profile(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - 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` + 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 ( + 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) + SELECT host_id, partner_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1) + 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())) @@ -675,10 +679,12 @@ func FetchUser(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - 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` + sql = `WITH best_scores AS (WITH RankedScores AS (SELECT sp.user_id, sp.map_id, sp.score_count, sp.score_time, ROW_NUMBER() OVER ( + 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) + SELECT user_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1) + 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())) @@ -729,10 +735,12 @@ func FetchUser(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - 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` + 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 ( + 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) + SELECT host_id, partner_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1) + 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())) diff --git a/backend/main.go b/backend/main.go index a50be7e..6499b51 100644 --- a/backend/main.go +++ b/backend/main.go @@ -4,11 +4,13 @@ import ( "fmt" "log" "os" + "time" "lphub/api" "lphub/database" _ "lphub/docs" + "github.com/gin-contrib/cors" "github.com/gin-gonic/gin" "github.com/joho/godotenv" ) @@ -34,19 +36,19 @@ func main() { database.ConnectDB() api.InitRoutes(router) // for debugging - // router.Use(cors.New(cors.Config{ - // AllowOrigins: []string{"*"}, - // AllowMethods: []string{"GET", "POST", "DELETE", "PUT", "PATCH"}, - // AllowHeaders: []string{"Origin"}, - // ExposeHeaders: []string{"Content-Length"}, - // AllowCredentials: true, - // MaxAge: 12 * time.Hour, - // })) - // router.Static("/static", "../frontend/build/static") - // router.StaticFile("/", "../frontend/build/index.html") - // router.NoRoute(func(c *gin.Context) { - // c.File("../frontend/build/index.html") - // }) + router.Use(cors.New(cors.Config{ + AllowOrigins: []string{"*"}, + AllowMethods: []string{"GET", "POST", "DELETE", "PUT", "PATCH"}, + AllowHeaders: []string{"Origin"}, + ExposeHeaders: []string{"Content-Length"}, + AllowCredentials: true, + MaxAge: 12 * time.Hour, + })) + router.Static("/static", "../frontend/build/static") + router.StaticFile("/", "../frontend/build/index.html") + router.NoRoute(func(c *gin.Context) { + c.File("../frontend/build/index.html") + }) router.MaxMultipartMemory = 200 << 20 // 200 mb limit for demos router.Run(fmt.Sprintf(":%s", os.Getenv("PORT"))) } -- cgit v1.2.3