From f2bfade4812a9f7b1f97593754c95359ce99e2f1 Mon Sep 17 00:00:00 2001 From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> Date: Sat, 2 Sep 2023 16:12:46 +0300 Subject: fix: optimize rankings Former-commit-id: 16b247861acea7b5c70a872db7b0397f9dc5c879 --- backend/handlers/home.go | 149 +++++++++++++++++++++++++---------------------- 1 file changed, 78 insertions(+), 71 deletions(-) (limited to 'backend/handlers/home.go') diff --git a/backend/handlers/home.go b/backend/handlers/home.go index 6e9a0df..53be1de 100644 --- a/backend/handlers/home.go +++ b/backend/handlers/home.go @@ -3,6 +3,7 @@ package handlers import ( "log" "net/http" + "sort" "strings" "github.com/gin-gonic/gin" @@ -16,8 +17,9 @@ type SearchResponse struct { } type RankingsResponse struct { - RankingsSP []models.UserRanking `json:"rankings_sp"` - RankingsMP []models.UserRanking `json:"rankings_mp"` + Overall []models.UserRanking `json:"rankings_overall"` + Singleplayer []models.UserRanking `json:"rankings_singleplayer"` + Multiplayer []models.UserRanking `json:"rankings_multiplayer"` } func Home(c *gin.Context) { @@ -40,96 +42,101 @@ func Home(c *gin.Context) { // @Failure 400 {object} models.Response // @Router /rankings [get] func Rankings(c *gin.Context) { - rows, err := database.DB.Query(`SELECT steam_id, user_name FROM users`) + response := RankingsResponse{ + Overall: []models.UserRanking{}, + Singleplayer: []models.UserRanking{}, + Multiplayer: []models.UserRanking{}, + } + // Singleplayer rankings + sql := `SELECT u.steam_id, u.user_name, COUNT(DISTINCT map_id), + (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g.is_coop = FALSE AND is_disabled = false), + (SELECT SUM(min_score_count) AS total_min_score_count FROM ( + SELECT + user_id, + MIN(score_count) AS min_score_count + FROM records_sp + GROUP BY user_id, map_id + ) AS subquery + WHERE user_id = u.steam_id) + FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id GROUP BY u.steam_id, u.user_name` + rows, err := database.DB.Query(sql) if err != nil { c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) return } - var spRankings []models.UserRanking - var mpRankings []models.UserRanking for rows.Next() { - var userID, username string - err := rows.Scan(&userID, &username) + ranking := models.UserRanking{} + var currentCount int + var totalCount int + err = rows.Scan(&ranking.UserID, &ranking.UserName, ¤tCount, &totalCount, &ranking.TotalScore) if err != nil { c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) return } - // Getting all sp records for each user - var uniqueSingleUserRecords, totalSingleMaps int - sql := `SELECT COUNT(DISTINCT map_id), (SELECT COUNT(map_name) FROM maps - WHERE is_coop = FALSE AND is_disabled = false) FROM records_sp WHERE user_id = $1` - err = database.DB.QueryRow(sql, userID).Scan(&uniqueSingleUserRecords, &totalSingleMaps) - if err != nil { - c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) - return + if currentCount != totalCount { + continue } - // Has all singleplayer records - if uniqueSingleUserRecords == totalSingleMaps { - var ranking models.UserRanking - ranking.UserID = userID - ranking.UserName = username - sql := `SELECT DISTINCT map_id, score_count FROM records_sp WHERE user_id = $1 ORDER BY map_id, score_count` - rows, err := database.DB.Query(sql, userID) - if err != nil { - c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) - return - } - totalScore := 0 - var maps []int - for rows.Next() { - var mapID, scoreCount int - rows.Scan(&mapID, &scoreCount) - if len(maps) != 0 && maps[len(maps)-1] == mapID { - continue - } - totalScore += scoreCount - maps = append(maps, mapID) - } - ranking.TotalScore = totalScore - spRankings = append(spRankings, ranking) - } - // Getting all mp records for each user - var uniqueMultiUserRecords, totalMultiMaps int - sql = `SELECT COUNT(DISTINCT map_id), (SELECT COUNT(map_name) FROM maps - WHERE is_coop = TRUE AND is_disabled = false) FROM records_mp WHERE host_id = $1 OR partner_id = $2` - err = database.DB.QueryRow(sql, userID, userID).Scan(&uniqueMultiUserRecords, &totalMultiMaps) + response.Singleplayer = append(response.Singleplayer, ranking) + } + // Multiplayer rankings + sql = `SELECT u.steam_id, u.user_name, COUNT(DISTINCT map_id), + (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g.is_coop = FALSE AND is_disabled = false), + (SELECT SUM(min_score_count) AS total_min_score_count FROM ( + SELECT + host_id, + partner_id, + MIN(score_count) AS min_score_count + FROM records_mp + GROUP BY host_id, partner_id, map_id + ) AS subquery + WHERE host_id = u.steam_id OR partner_id = u.steam_id) + FROM records_mp mp JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id GROUP BY u.steam_id, u.user_name` + rows, err = database.DB.Query(sql) + if err != nil { + c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) + return + } + for rows.Next() { + ranking := models.UserRanking{} + var currentCount int + var totalCount int + err = rows.Scan(&ranking.UserID, &ranking.UserName, ¤tCount, &totalCount, &ranking.TotalScore) if err != nil { c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) return } - // Has all singleplayer records - if uniqueMultiUserRecords == totalMultiMaps { - var ranking models.UserRanking - ranking.UserID = userID - ranking.UserName = username - sql := `SELECT DISTINCT map_id, score_count FROM records_mp WHERE host_id = $1 OR partner_id = $2 ORDER BY map_id, score_count` - rows, err := database.DB.Query(sql, userID, userID) - if err != nil { - c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) - return - } - totalScore := 0 - var maps []int - for rows.Next() { - var mapID, scoreCount int - rows.Scan(&mapID, &scoreCount) - if len(maps) != 0 && maps[len(maps)-1] == mapID { - continue + if currentCount != totalCount { + continue + } + response.Multiplayer = append(response.Multiplayer, ranking) + } + // Has both so they are qualified for overall ranking + for _, spRanking := range response.Singleplayer { + for _, mpRanking := range response.Multiplayer { + if spRanking.UserID == mpRanking.UserID { + totalScore := spRanking.TotalScore + mpRanking.TotalScore + overallRanking := models.UserRanking{ + UserID: spRanking.UserID, + UserName: spRanking.UserName, + TotalScore: totalScore, } - totalScore += scoreCount - maps = append(maps, mapID) + response.Overall = append(response.Overall, overallRanking) } - ranking.TotalScore = totalScore - mpRankings = append(mpRankings, ranking) } } + sort.Slice(response.Singleplayer, func(i, j int) bool { + return response.Singleplayer[i].TotalScore < response.Singleplayer[j].TotalScore + }) + sort.Slice(response.Multiplayer, func(i, j int) bool { + return response.Multiplayer[i].TotalScore < response.Multiplayer[j].TotalScore + }) + sort.Slice(response.Overall, func(i, j int) bool { + return response.Overall[i].TotalScore < response.Overall[j].TotalScore + }) c.JSON(http.StatusOK, models.Response{ Success: true, Message: "Successfully retrieved rankings.", - Data: RankingsResponse{ - RankingsSP: spRankings, - RankingsMP: mpRankings, - }, + Data: response, }) } -- cgit v1.2.3