From 7e63ef2fd6ce429d63ef73ee54199bc530da84fe Mon Sep 17 00:00:00 2001 From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> Date: Thu, 31 Oct 2024 00:29:37 +0300 Subject: backend: fix all rankings and placements, use sql funcs --- backend/database/functions.sql | 212 ++++++++++++++++++ backend/handlers/home.go | 56 +---- backend/handlers/user.go | 493 ++++++++++++++++------------------------- 3 files changed, 414 insertions(+), 347 deletions(-) create mode 100644 backend/database/functions.sql diff --git a/backend/database/functions.sql b/backend/database/functions.sql new file mode 100644 index 0000000..ca33a60 --- /dev/null +++ b/backend/database/functions.sql @@ -0,0 +1,212 @@ +CREATE OR REPLACE FUNCTION get_rankings_singleplayer() +RETURNS TABLE ( + steam_id TEXT, + user_name TEXT, + avatar_link TEXT, + total_min_score_count BIGINT +) AS $$ +BEGIN + RETURN QUERY + SELECT + u.steam_id, + u.user_name, + u.avatar_link, + ( + SELECT SUM(min_score_count) AS total_min_score_count + FROM ( + SELECT sp.user_id, MIN(sp.score_count) AS min_score_count + FROM records_sp sp + WHERE sp.is_deleted = false + GROUP BY sp.user_id, sp.map_id + ) AS subquery + WHERE user_id = u.steam_id + ) + FROM records_sp sp + JOIN users u ON u.steam_id = sp.user_id + WHERE sp.is_deleted = false + GROUP BY u.steam_id, u.user_name, u.avatar_link + HAVING COUNT(DISTINCT sp.map_id) = ( + SELECT COUNT(m.name) + FROM maps m + INNER JOIN games g ON m.game_id = g.id + WHERE g.id = 1 AND m.is_disabled = false + ) + ORDER BY total_min_score_count ASC; +END; +$$ LANGUAGE plpgsql; + + + +CREATE OR REPLACE FUNCTION get_rankings_multiplayer() +RETURNS TABLE ( + steam_id TEXT, + user_name TEXT, + avatar_link TEXT, + total_min_score_count BIGINT +) AS $$ +BEGIN + RETURN QUERY + SELECT + u.steam_id, + u.user_name, + u.avatar_link, + ( + SELECT SUM(min_score_count) AS total_min_score_count + FROM ( + SELECT DISTINCT ON (map_id, player_id) + map_id, + player_id, + MIN(score_count) AS min_score_count + FROM ( + SELECT + mp.map_id, + mp.host_id AS player_id, + mp.score_count + FROM records_mp mp + WHERE mp.is_deleted = false + UNION ALL + SELECT + mp.map_id, + mp.partner_id AS player_id, + mp.score_count + FROM records_mp mp + WHERE mp.is_deleted = false + ) AS player_scores + GROUP BY map_id, player_id + ) AS subquery + WHERE player_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 + WHERE mp.is_deleted = false + GROUP BY u.steam_id, u.user_name, u.avatar_link + HAVING COUNT(DISTINCT mp.map_id) = ( + SELECT COUNT(m.name) + FROM maps m + INNER JOIN games g ON m.game_id = g.id + WHERE g.id = 2 AND m.is_disabled = false + ) + ORDER BY total_min_score_count ASC; +END; +$$ LANGUAGE plpgsql; + + + +CREATE OR REPLACE FUNCTION get_placements_singleplayer(player_id TEXT) +RETURNS TABLE ( + map_id SMALLINT, + placement BIGINT +) AS $$ +BEGIN + RETURN QUERY + WITH ranked_scores AS ( + SELECT + sp.map_id, + sp.user_id, + sp.score_count, + sp.score_time, + ROW_NUMBER() OVER ( + PARTITION BY sp.map_id, sp.user_id + ORDER BY sp.score_count ASC, sp.score_time ASC + ) AS rank + FROM records_sp sp + WHERE sp.is_deleted = false + ), + best_scores AS ( + SELECT + rs.map_id, + rs.user_id, + rs.score_count, + rs.score_time + FROM ranked_scores rs + WHERE rs.rank = 1 + ), + min_placements AS ( + SELECT + bs.map_id, + bs.user_id, + (SELECT COUNT(*) + 1 + FROM best_scores AS inner_scores + WHERE inner_scores.map_id = bs.map_id + AND (inner_scores.score_count < bs.score_count + OR (inner_scores.score_count = bs.score_count + AND inner_scores.score_time < bs.score_time) + ) + ) AS placement + FROM best_scores AS bs + ) + SELECT + minp.map_id, + MIN(minp.placement) AS placement + FROM min_placements minp + WHERE minp.user_id = get_placements_singleplayer.player_id + GROUP BY minp.map_id + ORDER BY minp.map_id, placement; +END; +$$ LANGUAGE plpgsql; + + + +CREATE OR REPLACE FUNCTION get_placements_multiplayer(player_id TEXT) +RETURNS TABLE ( + map_id SMALLINT, + placement BIGINT +) AS $$ +BEGIN + RETURN QUERY + WITH ranked_scores AS ( + SELECT + mp.map_id, + mp.host_id, + mp.partner_id, + mp.score_count, + mp.score_time, + ROW_NUMBER() OVER ( + PARTITION BY mp.map_id, mp.host_id, mp.partner_id + ORDER BY mp.score_count ASC, mp.score_time ASC + ) AS rank + FROM records_mp mp + WHERE mp.is_deleted = false + ), + best_scores AS ( + SELECT + rs.map_id, + rs.host_id, + rs.partner_id, + rs.score_count, + rs.score_time + FROM ranked_scores rs + WHERE rs.rank = 1 + ), + min_placements AS ( + SELECT + bs.map_id, + bs.host_id, + bs.partner_id, + (SELECT COUNT(*) + 1 + FROM best_scores AS inner_scores + WHERE inner_scores.map_id = bs.map_id + AND (inner_scores.score_count < bs.score_count + OR (inner_scores.score_count = bs.score_count + AND inner_scores.score_time < bs.score_time) + ) + ) AS placement + FROM best_scores AS bs + ), + distinct_min_placements AS ( + SELECT unified_placements.map_id, unified_placements.player_id, MIN(unified_placements.placement) AS min_placement + FROM ( + SELECT minp.map_id, minp.host_id AS player_id, minp.placement FROM min_placements minp + UNION ALL + SELECT minp.map_id, minp.partner_id AS player_id, minp.placement FROM min_placements minp + ) AS unified_placements + WHERE unified_placements.player_id = get_placements_multiplayer.player_id + GROUP BY unified_placements.map_id, unified_placements.player_id + ) + SELECT + dminp.map_id, + dminp.min_placement AS placement + FROM distinct_min_placements dminp + ORDER BY dminp.map_id, placement; +END; +$$ LANGUAGE plpgsql; diff --git a/backend/handlers/home.go b/backend/handlers/home.go index 57e44c1..714610a 100644 --- a/backend/handlers/home.go +++ b/backend/handlers/home.go @@ -6,7 +6,6 @@ import ( "log" "net/http" "os" - "sort" "strings" "lphub/database" @@ -64,66 +63,33 @@ func RankingsLPHUB(c *gin.Context) { Overall: []models.UserRanking{}, } // Singleplayer rankings - sql := `SELECT u.steam_id, u.user_name, u.avatar_link, COUNT(DISTINCT map_id), - (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g."name" = 'Portal 2 - Singleplayer' 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 WHERE is_deleted = false - 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 WHERE is_deleted = false GROUP BY u.steam_id, u.user_name` - rows, err := database.DB.Query(sql) + rows, err := database.DB.Query(`SELECT * FROM get_rankings_singleplayer();`) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } for rows.Next() { ranking := models.UserRanking{} - var currentCount int - var totalCount int - err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, ¤tCount, &totalCount, &ranking.TotalScore) + err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - if currentCount != totalCount { - continue - } response.Singleplayer = append(response.Singleplayer, ranking) } // Multiplayer rankings - sql = `SELECT u.steam_id, u.user_name, u.avatar_link, COUNT(DISTINCT map_id), - (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g."name" = 'Portal 2 - Cooperative' 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 WHERE is_deleted = false - 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 WHERE is_deleted = false GROUP BY u.steam_id, u.user_name` - rows, err = database.DB.Query(sql) + rows, err = database.DB.Query(`SELECT * FROM get_rankings_multiplayer();`) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } for rows.Next() { ranking := models.UserRanking{} - var currentCount int - var totalCount int - err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, ¤tCount, &totalCount, &ranking.TotalScore) + err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - if currentCount != totalCount { - continue - } response.Multiplayer = append(response.Multiplayer, ranking) } // Has both so they are qualified for overall ranking @@ -136,12 +102,11 @@ func RankingsLPHUB(c *gin.Context) { TotalScore: totalScore, } response.Overall = append(response.Overall, overallRanking) + break } } } - sort.Slice(response.Singleplayer, func(i, j int) bool { - return response.Singleplayer[i].TotalScore < response.Singleplayer[j].TotalScore - }) + placement := 1 ties := 0 for index := 0; index < len(response.Singleplayer); index++ { @@ -154,9 +119,7 @@ func RankingsLPHUB(c *gin.Context) { } placement++ } - sort.Slice(response.Multiplayer, func(i, j int) bool { - return response.Multiplayer[i].TotalScore < response.Multiplayer[j].TotalScore - }) + placement = 1 ties = 0 for index := 0; index < len(response.Multiplayer); index++ { @@ -169,9 +132,7 @@ func RankingsLPHUB(c *gin.Context) { } placement++ } - sort.Slice(response.Overall, func(i, j int) bool { - return response.Overall[i].TotalScore < response.Overall[j].TotalScore - }) + placement = 1 ties = 0 for index := 0; index < len(response.Overall); index++ { @@ -184,6 +145,7 @@ func RankingsLPHUB(c *gin.Context) { } placement++ } + c.JSON(http.StatusOK, models.Response{ Success: true, Message: "Successfully retrieved rankings.", diff --git a/backend/handlers/user.go b/backend/handlers/user.go index dc058c8..021a47f 100644 --- a/backend/handlers/user.go +++ b/backend/handlers/user.go @@ -134,162 +134,118 @@ func Profile(c *gin.Context) { } } rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount - // Get user ranking placement for singleplayer - sql = `SELECT u.steam_id, COUNT(DISTINCT map_id), - (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g."name" = 'Portal 2 - Singleplayer' AND maps.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 WHERE is_deleted = false 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 WHERE is_deleted = false GROUP BY u.steam_id, u.user_name - HAVING 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) - ORDER BY total_min_score_count ASC` - rows, err = database.DB.Query(sql) + // Get user rankings. We are basically doing the same thing in RankingsLPHUB endpoint lol. + rankingsList := RankingsResponse{ + Singleplayer: []models.UserRanking{}, + Multiplayer: []models.UserRanking{}, + Overall: []models.UserRanking{}, + } + // Singleplayer rankings + rows, err = database.DB.Query(`SELECT * FROM get_rankings_singleplayer();`) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - placement := 1 for rows.Next() { - var steamID string - var completionCount int - var totalCount int - var userPortalCount int - err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount) + ranking := models.UserRanking{} + err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - if completionCount != totalCount { - placement++ - continue - } - if steamID != user.(models.User).SteamID { - placement++ - continue - } - rankings.Singleplayer.Rank = placement - } - // Get user ranking placement for multiplayer - sql = `SELECT u.steam_id, COUNT(DISTINCT map_id), - (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g."name" = 'Portal 2 - Cooperative' 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 WHERE is_deleted = false 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 WHERE mp.is_deleted = false GROUP BY u.steam_id, u.user_name - HAVING COUNT(DISTINCT map_id) = (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g.is_coop = true AND is_disabled = false) - ORDER BY total_min_score_count ASC` - rows, err = database.DB.Query(sql) + rankingsList.Singleplayer = append(rankingsList.Singleplayer, ranking) + } + // Multiplayer rankings + rows, err = database.DB.Query(`SELECT * FROM get_rankings_multiplayer();`) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - placement = 1 for rows.Next() { - var steamID string - var completionCount int - var totalCount int - var userPortalCount int - err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount) + ranking := models.UserRanking{} + err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - if completionCount != totalCount { - placement++ - continue + rankingsList.Multiplayer = append(rankingsList.Multiplayer, ranking) + } + // Overall rankings + var hasOverallPlacement bool + for _, spRanking := range rankingsList.Singleplayer { + for _, mpRanking := range rankingsList.Multiplayer { + if spRanking.User.SteamID == mpRanking.User.SteamID { + if spRanking.User.SteamID == user.(models.User).SteamID { + hasOverallPlacement = true + } + totalScore := spRanking.TotalScore + mpRanking.TotalScore + overallRanking := models.UserRanking{ + User: spRanking.User, + TotalScore: totalScore, + } + rankingsList.Overall = append(rankingsList.Overall, overallRanking) + break + } } - if steamID != user.(models.User).SteamID { - placement++ - continue + } + + placement := 1 + ties := 0 + for index := 0; index < len(rankingsList.Singleplayer); index++ { + if index != 0 && rankingsList.Singleplayer[index-1].TotalScore == rankingsList.Singleplayer[index].TotalScore { + ties++ + rankingsList.Singleplayer[index].Placement = placement - ties + } else { + ties = 0 + rankingsList.Singleplayer[index].Placement = placement } - rankings.Cooperative.Rank = placement - } - // Get user ranking placement for overall if they qualify - if rankings.Singleplayer.Rank != 0 && rankings.Cooperative.Rank != 0 { - sql = `WITH user_sp AS ( - SELECT u.steam_id, - SUM(subquery.min_score_count) AS total_min_score_count - FROM users u - LEFT JOIN ( - SELECT user_id, map_id, MIN(score_count) AS min_score_count - FROM records_sp WHERE is_deleted = false - GROUP BY user_id, map_id - ) AS subquery ON subquery.user_id = u.steam_id - WHERE u.steam_id IN ( - SELECT user_id - FROM records_sp sp - JOIN maps m ON sp.map_id = m.id - JOIN games g ON m.game_id = g.id - WHERE g.is_coop = FALSE AND m.is_disabled = FALSE AND sp.is_deleted = false - GROUP BY user_id - HAVING COUNT(DISTINCT sp.map_id) = ( - SELECT COUNT(maps.name) - FROM maps - INNER JOIN games g ON maps.game_id = g.id - WHERE g.is_coop = FALSE AND maps.is_disabled = FALSE - ) - ) - GROUP BY u.steam_id - ), user_mp AS ( - SELECT u.steam_id, - SUM(subquery.min_score_count) AS total_min_score_count - FROM users u - LEFT JOIN ( - SELECT host_id, partner_id, map_id, MIN(score_count) AS min_score_count - FROM records_mp WHERE is_deleted = false - GROUP BY host_id, partner_id, map_id - ) AS subquery ON subquery.host_id = u.steam_id OR subquery.partner_id = u.steam_id - WHERE u.steam_id IN ( - SELECT host_id - FROM records_mp mp - JOIN maps m ON mp.map_id = m.id - JOIN games g ON m.game_id = g.id - WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false - GROUP BY host_id - HAVING COUNT(DISTINCT mp.map_id) = ( - SELECT COUNT(maps.name) - FROM maps - INNER JOIN games g ON maps.game_id = g.id - WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE - ) - UNION - SELECT partner_id - FROM records_mp mp - JOIN maps m ON mp.map_id = m.id - JOIN games g ON m.game_id = g.id - WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false - GROUP BY partner_id - HAVING COUNT(DISTINCT mp.map_id) = ( - SELECT COUNT(maps.name) - FROM maps - INNER JOIN games g ON maps.game_id = g.id - WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE - ) - ) - GROUP BY u.steam_id - ) - SELECT COALESCE(sp.steam_id, mp.steam_id) AS steam_id, - sp.total_min_score_count + mp.total_min_score_count AS overall_total_min_score_count - FROM user_sp sp - INNER JOIN user_mp mp ON sp.steam_id = mp.steam_id - ORDER BY overall_total_min_score_count ASC` - rows, err = database.DB.Query(sql) - if err != nil { - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return + placement++ + } + + placement = 1 + ties = 0 + for index := 0; index < len(rankingsList.Multiplayer); index++ { + if index != 0 && rankingsList.Multiplayer[index-1].TotalScore == rankingsList.Multiplayer[index].TotalScore { + ties++ + rankingsList.Multiplayer[index].Placement = placement - ties + } else { + ties = 0 + rankingsList.Multiplayer[index].Placement = placement } - placement = 1 - for rows.Next() { - var steamID string - var userPortalCount int - err = rows.Scan(&steamID, &userPortalCount) - if err != nil { - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return - } - if steamID != user.(models.User).SteamID { - placement++ - continue + placement++ + } + + placement = 1 + ties = 0 + for index := 0; index < len(rankingsList.Overall); index++ { + if index != 0 && rankingsList.Overall[index-1].TotalScore == rankingsList.Overall[index].TotalScore { + ties++ + rankingsList.Overall[index].Placement = placement - ties + } else { + ties = 0 + rankingsList.Overall[index].Placement = placement + } + placement++ + } + // After we did that heavy calculation and got the rankings of ALL players, let's see if our user exists and grab the placements if they do. + for _, singleplayer := range rankingsList.Singleplayer { + if singleplayer.User.SteamID == user.(models.User).SteamID { + rankings.Singleplayer.Rank = singleplayer.Placement + break + } + } + for _, multiplayer := range rankingsList.Multiplayer { + if multiplayer.User.SteamID == user.(models.User).SteamID { + rankings.Cooperative.Rank = multiplayer.Placement + break + } + } + if hasOverallPlacement { + for _, overall := range rankingsList.Overall { + if overall.User.SteamID == user.(models.User).SteamID { + rankings.Overall.Rank = overall.Placement + break } - rankings.Overall.Rank = placement } } records := []ProfileRecords{} @@ -301,13 +257,7 @@ func Profile(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - 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) + placementsRows, err := database.DB.Query(`SELECT * FROM get_placements_singleplayer($1);`, user.(models.User).SteamID) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return @@ -315,8 +265,9 @@ func Profile(c *gin.Context) { placements := []int{} placementIndex := 0 for placementsRows.Next() { + var mapID int var placement int - placementsRows.Scan(&placement) + placementsRows.Scan(&mapID, &placement) placements = append(placements, placement) } for rows.Next() { @@ -357,13 +308,7 @@ func Profile(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - 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) + placementsRows, err = database.DB.Query(`SELECT * FROM get_placements_multiplayer($1);`, user.(models.User).SteamID) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return @@ -371,8 +316,9 @@ func Profile(c *gin.Context) { placements = []int{} placementIndex = 0 for placementsRows.Next() { + var mapID int var placement int - placementsRows.Scan(&placement) + placementsRows.Scan(&mapID, &placement) placements = append(placements, placement) } for rows.Next() { @@ -512,162 +458,119 @@ func FetchUser(c *gin.Context) { } } rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount - // Get user ranking placement for singleplayer - sql = `SELECT u.steam_id, COUNT(DISTINCT map_id), - (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g."name" = 'Portal 2 - Singleplayer' AND maps.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 WHERE is_deleted = false 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 WHERE is_deleted = false GROUP BY u.steam_id, u.user_name - HAVING 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) - ORDER BY total_min_score_count ASC` - rows, err = database.DB.Query(sql) + // Get user rankings. We are basically doing the same thing in RankingsLPHUB endpoint lol. + rankingsList := RankingsResponse{ + Singleplayer: []models.UserRanking{}, + Multiplayer: []models.UserRanking{}, + Overall: []models.UserRanking{}, + } + // Singleplayer rankings + rows, err = database.DB.Query(`SELECT * FROM get_rankings_singleplayer();`) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - placement := 1 for rows.Next() { - var steamID string - var completionCount int - var totalCount int - var userPortalCount int - err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount) + ranking := models.UserRanking{} + err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - if completionCount != totalCount { - placement++ - continue - } - if steamID != user.SteamID { - placement++ - continue - } - rankings.Singleplayer.Rank = placement - } - // Get user ranking placement for multiplayer - sql = `SELECT u.steam_id, COUNT(DISTINCT map_id), - (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g."name" = 'Portal 2 - Cooperative' 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 WHERE is_deleted = false 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 WHERE mp.is_deleted = false GROUP BY u.steam_id, u.user_name - HAVING COUNT(DISTINCT map_id) = (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g.is_coop = true AND is_disabled = false) - ORDER BY total_min_score_count ASC` - rows, err = database.DB.Query(sql) + rankingsList.Singleplayer = append(rankingsList.Singleplayer, ranking) + } + // Multiplayer rankings + rows, err = database.DB.Query(`SELECT * FROM get_rankings_multiplayer();`) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - placement = 1 for rows.Next() { - var steamID string - var completionCount int - var totalCount int - var userPortalCount int - err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount) + ranking := models.UserRanking{} + err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - if completionCount != totalCount { - placement++ - continue + rankingsList.Multiplayer = append(rankingsList.Multiplayer, ranking) + } + // Overall rankings + var hasOverallPlacement bool + for _, spRanking := range rankingsList.Singleplayer { + for _, mpRanking := range rankingsList.Multiplayer { + if spRanking.User.SteamID == mpRanking.User.SteamID { + if spRanking.User.SteamID == user.SteamID { + hasOverallPlacement = true + } + totalScore := spRanking.TotalScore + mpRanking.TotalScore + overallRanking := models.UserRanking{ + User: spRanking.User, + TotalScore: totalScore, + } + rankingsList.Overall = append(rankingsList.Overall, overallRanking) + break + } } - if steamID != user.SteamID { - placement++ - continue + } + + placement := 1 + ties := 0 + for index := 0; index < len(rankingsList.Singleplayer); index++ { + if index != 0 && rankingsList.Singleplayer[index-1].TotalScore == rankingsList.Singleplayer[index].TotalScore { + ties++ + rankingsList.Singleplayer[index].Placement = placement - ties + } else { + ties = 0 + rankingsList.Singleplayer[index].Placement = placement } - rankings.Cooperative.Rank = placement - } - // Get user ranking placement for overall if they qualify - if rankings.Singleplayer.Rank != 0 && rankings.Cooperative.Rank != 0 { - sql = `WITH user_sp AS ( - SELECT u.steam_id, - SUM(subquery.min_score_count) AS total_min_score_count - FROM users u - LEFT JOIN ( - SELECT user_id, map_id, MIN(score_count) AS min_score_count - FROM records_sp WHERE is_deleted = false - GROUP BY user_id, map_id - ) AS subquery ON subquery.user_id = u.steam_id - WHERE u.steam_id IN ( - SELECT user_id - FROM records_sp sp - JOIN maps m ON sp.map_id = m.id - JOIN games g ON m.game_id = g.id - WHERE g.is_coop = FALSE AND m.is_disabled = FALSE AND sp.is_deleted = false - GROUP BY user_id - HAVING COUNT(DISTINCT sp.map_id) = ( - SELECT COUNT(maps.name) - FROM maps - INNER JOIN games g ON maps.game_id = g.id - WHERE g.is_coop = FALSE AND maps.is_disabled = FALSE - ) - ) - GROUP BY u.steam_id - ), user_mp AS ( - SELECT u.steam_id, - SUM(subquery.min_score_count) AS total_min_score_count - FROM users u - LEFT JOIN ( - SELECT host_id, partner_id, map_id, MIN(score_count) AS min_score_count - FROM records_mp WHERE is_deleted = false - GROUP BY host_id, partner_id, map_id - ) AS subquery ON subquery.host_id = u.steam_id OR subquery.partner_id = u.steam_id - WHERE u.steam_id IN ( - SELECT host_id - FROM records_mp mp - JOIN maps m ON mp.map_id = m.id - JOIN games g ON m.game_id = g.id - WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false - GROUP BY host_id - HAVING COUNT(DISTINCT mp.map_id) = ( - SELECT COUNT(maps.name) - FROM maps - INNER JOIN games g ON maps.game_id = g.id - WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE - ) - UNION - SELECT partner_id - FROM records_mp mp - JOIN maps m ON mp.map_id = m.id - JOIN games g ON m.game_id = g.id - WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false - GROUP BY partner_id - HAVING COUNT(DISTINCT mp.map_id) = ( - SELECT COUNT(maps.name) - FROM maps - INNER JOIN games g ON maps.game_id = g.id - WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE - ) - ) - GROUP BY u.steam_id - ) - SELECT COALESCE(sp.steam_id, mp.steam_id) AS steam_id, - sp.total_min_score_count + mp.total_min_score_count AS overall_total_min_score_count - FROM user_sp sp - INNER JOIN user_mp mp ON sp.steam_id = mp.steam_id - ORDER BY overall_total_min_score_count ASC` - rows, err = database.DB.Query(sql) - if err != nil { - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return + placement++ + } + + placement = 1 + ties = 0 + for index := 0; index < len(rankingsList.Multiplayer); index++ { + if index != 0 && rankingsList.Multiplayer[index-1].TotalScore == rankingsList.Multiplayer[index].TotalScore { + ties++ + rankingsList.Multiplayer[index].Placement = placement - ties + } else { + ties = 0 + rankingsList.Multiplayer[index].Placement = placement } - placement = 1 - for rows.Next() { - var steamID string - var userPortalCount int - err = rows.Scan(&steamID, &userPortalCount) - if err != nil { - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return - } - if steamID != user.SteamID { - placement++ - continue + placement++ + } + + placement = 1 + ties = 0 + for index := 0; index < len(rankingsList.Overall); index++ { + if index != 0 && rankingsList.Overall[index-1].TotalScore == rankingsList.Overall[index].TotalScore { + ties++ + rankingsList.Overall[index].Placement = placement - ties + } else { + ties = 0 + rankingsList.Overall[index].Placement = placement + } + placement++ + } + // After we did that heavy calculation and got the rankings of ALL players, let's see if our user exists + // and grab the placements if they do. + for _, singleplayer := range rankingsList.Singleplayer { + if singleplayer.User.SteamID == user.SteamID { + rankings.Singleplayer.Rank = singleplayer.Placement + break + } + } + for _, multiplayer := range rankingsList.Multiplayer { + if multiplayer.User.SteamID == user.SteamID { + rankings.Cooperative.Rank = multiplayer.Placement + break + } + } + if hasOverallPlacement { + for _, overall := range rankingsList.Overall { + if overall.User.SteamID == user.SteamID { + rankings.Overall.Rank = overall.Placement + break } - rankings.Overall.Rank = placement } } records := []ProfileRecords{} @@ -679,13 +582,7 @@ func FetchUser(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - 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) + placementsRows, err := database.DB.Query(`SELECT * FROM get_placements_singleplayer($1);`, user.SteamID) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return @@ -693,8 +590,9 @@ func FetchUser(c *gin.Context) { placements := []int{} placementIndex := 0 for placementsRows.Next() { + var mapID int var placement int - placementsRows.Scan(&placement) + placementsRows.Scan(&mapID, &placement) placements = append(placements, placement) } for rows.Next() { @@ -735,13 +633,7 @@ func FetchUser(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - 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) + placementsRows, err = database.DB.Query(`SELECT * FROM get_placements_multiplayer($1);`, user.SteamID) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return @@ -749,8 +641,9 @@ func FetchUser(c *gin.Context) { placements = []int{} placementIndex = 0 for placementsRows.Next() { + var mapID int var placement int - placementsRows.Scan(&placement) + placementsRows.Scan(&mapID, &placement) placements = append(placements, placement) } for rows.Next() { -- cgit v1.2.3