feat: completed profile rankings (#51)

Former-commit-id: a2ef452a32803181f6937f08840e5f0e55ed6403
author: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> 2023-09-25 22:43:55 +0300
committer: GitHub <noreply@github.com> 2023-09-25 22:43:55 +0300
commit: 7f420a2c34667ed7addab356b17e4be2612ecc0b (patch)
tree: 75196ccaa66d3488923634580f20a52973787704 /backend/handlers/user.go
parent: docs: add delete record, update discussion and main (#56) (diff)
download: lphub-7f420a2c34667ed7addab356b17e4be2612ecc0b.tar.gz
lphub-7f420a2c34667ed7addab356b17e4be2612ecc0b.tar.bz2
lphub-7f420a2c34667ed7addab356b17e4be2612ecc0b.zip
1 files changed, 187 insertions, 117 deletions
diff --git a/backend/handlers/user.go b/backend/handlers/user.go
index f04145e..777d60c 100644
--- a/backend/handlers/user.go
+++ b/backend/handlers/user.go
@@ -132,7 +132,7 @@ 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.is_coop = FALSE AND is_disabled = false), 
+        (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 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
@@ -166,11 +166,11 @@ func Profile(c *gin.Context) {
        }
        // 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.is_coop = FALSE AND is_disabled = false), 
+        (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 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
-        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)
+        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)
        if err != nil {
@@ -198,61 +198,96 @@ func Profile(c *gin.Context) {
                }
                rankings.Cooperative.Rank = placement
        }
-        // TODO: Get user ranking placement for overall if they qualify
+        // Get user ranking placement for overall if they qualify
-        // if (rankings.Singleplayer.Rank != 0) && (rankings.Cooperative.Rank != 0) {
+        if rankings.Singleplayer.Rank != 0 && rankings.Cooperative.Rank != 0 {
-        //      sql = `SELECT steam_id, SUM(total_min_score_count) AS total_score
+                sql = `WITH user_sp AS (
-        // FROM (
+                        SELECT u.steam_id,
-        //      SELECT u.steam_id,
+                                   SUM(subquery.min_score_count) AS total_min_score_count
-        //                 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
+                        FROM users u
-        //                              SELECT
+                        LEFT JOIN (
-        //                                      user_id,
+                                SELECT user_id, map_id, MIN(score_count) AS min_score_count
-        //                                      MIN(score_count) AS min_score_count
+                                FROM records_sp
-        //                              FROM records_sp
+                                GROUP BY user_id, map_id
-        //                              GROUP BY user_id, map_id
+                        ) AS subquery ON subquery.user_id = u.steam_id
-        //                      ) AS subquery
+                        WHERE u.steam_id IN (
-        //                      WHERE user_id = u.steam_id) AS total_min_score_count
+                                SELECT user_id
-        //      FROM records_sp sp
+                                FROM records_sp sp
-        //      JOIN users u ON u.steam_id = sp.user_id
+                                JOIN maps m ON sp.map_id = m.id
-        //      UNION ALL
+                                JOIN games g ON m.game_id = g.id
-        //      SELECT u.steam_id,
+                                WHERE g.is_coop = FALSE AND m.is_disabled = FALSE
-        //                 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
+                                GROUP BY user_id
-        //                              SELECT
+                                HAVING COUNT(DISTINCT sp.map_id) = (
-        //                                      host_id,
+                                        SELECT COUNT(maps.name)
-        //                                      partner_id,
+                                        FROM maps
-        //                                      MIN(score_count) AS min_score_count
+                                        INNER JOIN games g ON maps.game_id = g.id
-        //                              FROM records_mp
+                                        WHERE g.is_coop = FALSE AND maps.is_disabled = FALSE
-        //                              GROUP BY host_id, partner_id, map_id
+                                )
-        //                      ) AS subquery
+                        )
-        //                      WHERE host_id = u.steam_id OR partner_id = u.steam_id) AS total_min_score_count
+                        GROUP BY u.steam_id
-        //      FROM records_mp mp
+                ), user_mp AS (
-        //      JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id
+                        SELECT u.steam_id,
-        // ) AS combined_scores
+                                   SUM(subquery.min_score_count) AS total_min_score_count
-        // GROUP BY steam_id ORDER BY total_score ASC;`
+                        FROM users u
-        //      rows, err = database.DB.Query(sql)
+                        LEFT JOIN (
-        //      if err != nil {
+                                SELECT host_id, partner_id, map_id, MIN(score_count) AS min_score_count
-        //              c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                                FROM records_mp
-        //              return
+                                GROUP BY host_id, partner_id, map_id
-        //      }
+                        ) AS subquery ON subquery.host_id = u.steam_id OR subquery.partner_id = u.steam_id
-        //      placement = 1
+                        WHERE u.steam_id IN (
-        //      for rows.Next() {
+                                SELECT host_id
-        //              var steamID string
+                                FROM records_mp mp
-        //              var userPortalCount int
+                                JOIN maps m ON mp.map_id = m.id
-        //              err = rows.Scan(&steamID, &userPortalCount)
+                                JOIN games g ON m.game_id = g.id
-        //              if err != nil {
+                                WHERE g.is_coop = TRUE AND m.is_disabled = FALSE
-        //                      c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                                GROUP BY host_id
-        //                      return
+                                HAVING COUNT(DISTINCT mp.map_id) = (
-        //              }
+                                        SELECT COUNT(maps.name)
-        //              if completionCount != totalCount {
+                                        FROM maps
-        //                      placement++
+                                        INNER JOIN games g ON maps.game_id = g.id
-        //                      continue
+                                        WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
-        //              }
+                                )
-        //              if steamID != user.(models.User).SteamID {
+                                UNION
-        //                      placement++
+                                SELECT partner_id
-        //                      continue
+                                FROM records_mp mp
-        //              }
+                                JOIN maps m ON mp.map_id = m.id
-        //              rankings.Cooperative.Rank = placement
+                                JOIN games g ON m.game_id = g.id
-        //      }
+                                WHERE g.is_coop = TRUE AND m.is_disabled = 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,
+                           COALESCE(sp.total_min_score_count, 0) + COALESCE(mp.total_min_score_count, 0) 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 = 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
+                        }
+                        rankings.Overall.Rank = placement
+                }
+        }
        records := []ProfileRecords{}
        // Get singleplayer records
        sql = `SELECT m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = sp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date
@@ -419,7 +454,7 @@ 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.is_coop = FALSE AND is_disabled = false), 
+        (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 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
@@ -453,11 +488,11 @@ func FetchUser(c *gin.Context) {
        }
        // 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.is_coop = FALSE AND is_disabled = false), 
+        (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 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
-        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)
+        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)
        if err != nil {
@@ -485,61 +520,96 @@ func FetchUser(c *gin.Context) {
                }
                rankings.Cooperative.Rank = placement
        }
-        // TODO: Get user ranking placement for overall if they qualify
+        // Get user ranking placement for overall if they qualify
-        // if (rankings.Singleplayer.Rank != 0) && (rankings.Cooperative.Rank != 0) {
+        if rankings.Singleplayer.Rank != 0 && rankings.Cooperative.Rank != 0 {
-        //      sql = `SELECT steam_id, SUM(total_min_score_count) AS total_score
+                sql = `WITH user_sp AS (
-        // FROM (
+                        SELECT u.steam_id,
-        //      SELECT u.steam_id,
+                                   SUM(subquery.min_score_count) AS total_min_score_count
-        //                 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
+                        FROM users u
-        //                              SELECT
+                        LEFT JOIN (
-        //                                      user_id,
+                                SELECT user_id, map_id, MIN(score_count) AS min_score_count
-        //                                      MIN(score_count) AS min_score_count
+                                FROM records_sp
-        //                              FROM records_sp
+                                GROUP BY user_id, map_id
-        //                              GROUP BY user_id, map_id
+                        ) AS subquery ON subquery.user_id = u.steam_id
-        //                      ) AS subquery
+                        WHERE u.steam_id IN (
-        //                      WHERE user_id = u.steam_id) AS total_min_score_count
+                                SELECT user_id
-        //      FROM records_sp sp
+                                FROM records_sp sp
-        //      JOIN users u ON u.steam_id = sp.user_id
+                                JOIN maps m ON sp.map_id = m.id
-        //      UNION ALL
+                                JOIN games g ON m.game_id = g.id
-        //      SELECT u.steam_id,
+                                WHERE g.is_coop = FALSE AND m.is_disabled = FALSE
-        //                 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
+                                GROUP BY user_id
-        //                              SELECT
+                                HAVING COUNT(DISTINCT sp.map_id) = (
-        //                                      host_id,
+                                        SELECT COUNT(maps.name)
-        //                                      partner_id,
+                                        FROM maps
-        //                                      MIN(score_count) AS min_score_count
+                                        INNER JOIN games g ON maps.game_id = g.id
-        //                              FROM records_mp
+                                        WHERE g.is_coop = FALSE AND maps.is_disabled = FALSE
-        //                              GROUP BY host_id, partner_id, map_id
+                                )
-        //                      ) AS subquery
+                        )
-        //                      WHERE host_id = u.steam_id OR partner_id = u.steam_id) AS total_min_score_count
+                        GROUP BY u.steam_id
-        //      FROM records_mp mp
+                ), user_mp AS (
-        //      JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id
+                        SELECT u.steam_id,
-        // ) AS combined_scores
+                                   SUM(subquery.min_score_count) AS total_min_score_count
-        // GROUP BY steam_id ORDER BY total_score ASC;`
+                        FROM users u
-        //      rows, err = database.DB.Query(sql)
+                        LEFT JOIN (
-        //      if err != nil {
+                                SELECT host_id, partner_id, map_id, MIN(score_count) AS min_score_count
-        //              c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                                FROM records_mp
-        //              return
+                                GROUP BY host_id, partner_id, map_id
-        //      }
+                        ) AS subquery ON subquery.host_id = u.steam_id OR subquery.partner_id = u.steam_id
-        //      placement = 1
+                        WHERE u.steam_id IN (
-        //      for rows.Next() {
+                                SELECT host_id
-        //              var steamID string
+                                FROM records_mp mp
-        //              var userPortalCount int
+                                JOIN maps m ON mp.map_id = m.id
-        //              err = rows.Scan(&steamID, &userPortalCount)
+                                JOIN games g ON m.game_id = g.id
-        //              if err != nil {
+                                WHERE g.is_coop = TRUE AND m.is_disabled = FALSE
-        //                      c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                                GROUP BY host_id
-        //                      return
+                                HAVING COUNT(DISTINCT mp.map_id) = (
-        //              }
+                                        SELECT COUNT(maps.name)
-        //              if completionCount != totalCount {
+                                        FROM maps
-        //                      placement++
+                                        INNER JOIN games g ON maps.game_id = g.id
-        //                      continue
+                                        WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
-        //              }
+                                )
-        //              if steamID != user.SteamID {
+                                UNION
-        //                      placement++
+                                SELECT partner_id
-        //                      continue
+                                FROM records_mp mp
-        //              }
+                                JOIN maps m ON mp.map_id = m.id
-        //              rankings.Cooperative.Rank = placement
+                                JOIN games g ON m.game_id = g.id
-        //      }
+                                WHERE g.is_coop = TRUE AND m.is_disabled = 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,
+                           COALESCE(sp.total_min_score_count, 0) + COALESCE(mp.total_min_score_count, 0) 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 = 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
+                        }
+                        rankings.Overall.Rank = placement
+                }
+        }
        records := []ProfileRecords{}
        // Get singleplayer records
        sql = `SELECT m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = sp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date
@@ -609,7 +679,7 @@ func FetchUser(c *gin.Context) {
                Success: true,
                Message: "Successfully retrieved user scores.",
                Data: ProfileResponse{
-                        Profile:     true,
+                        Profile:     false,
                        SteamID:     user.SteamID,
                        UserName:    user.UserName,
                        AvatarLink:  user.AvatarLink,
author	Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>	2023-09-25 22:43:55 +0300
committer	GitHub <noreply@github.com>	2023-09-25 22:43:55 +0300
commit	7f420a2c34667ed7addab356b17e4be2612ecc0b (patch)
tree	75196ccaa66d3488923634580f20a52973787704 /backend/handlers/user.go
parent	docs: add delete record, update discussion and main (#56) (diff)
download	lphub-7f420a2c34667ed7addab356b17e4be2612ecc0b.tar.gz lphub-7f420a2c34667ed7addab356b17e4be2612ecc0b.tar.bz2 lphub-7f420a2c34667ed7addab356b17e4be2612ecc0b.zip

diff --git a/backend/handlers/user.go b/backend/handlers/user.go index f04145e..777d60c 100644 --- a/backend/handlers/user.go +++ b/backend/handlers/user.go
@@ -132,7 +132,7 @@ func Profile(c *gin.Context) {
132	rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount	132	rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount
133	// Get user ranking placement for singleplayer	133	// Get user ranking placement for singleplayer
134	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),	134	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),
135	(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),	135	(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),
136	(SELECT SUM(min_score_count) AS total_min_score_count FROM (	136	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
137	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)	137	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)
138	FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id GROUP BY u.steam_id, u.user_name	138	FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id GROUP BY u.steam_id, u.user_name
@@ -166,11 +166,11 @@ func Profile(c *gin.Context) {
166	}	166	}
167	// Get user ranking placement for multiplayer	167	// Get user ranking placement for multiplayer
168	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),	168	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),
169	(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),	169	(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),
170	(SELECT SUM(min_score_count) AS total_min_score_count FROM (	170	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
171	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)	171	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)
172	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	172	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
173	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)	173	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)
174	ORDER BY total_min_score_count ASC;`	174	ORDER BY total_min_score_count ASC;`
175	rows, err = database.DB.Query(sql)	175	rows, err = database.DB.Query(sql)
176	if err != nil {	176	if err != nil {
@@ -198,61 +198,96 @@ func Profile(c *gin.Context) {
198	}	198	}
199	rankings.Cooperative.Rank = placement	199	rankings.Cooperative.Rank = placement
200	}	200	}
201	// TODO: Get user ranking placement for overall if they qualify	201	// Get user ranking placement for overall if they qualify
202	// if (rankings.Singleplayer.Rank != 0) && (rankings.Cooperative.Rank != 0) {	202	if rankings.Singleplayer.Rank != 0 && rankings.Cooperative.Rank != 0 {
203	// sql = `SELECT steam_id, SUM(total_min_score_count) AS total_score	203	sql = `WITH user_sp AS (
204	// FROM (	204	SELECT u.steam_id,
205	// SELECT u.steam_id,	205	SUM(subquery.min_score_count) AS total_min_score_count
206	// (SELECT SUM(min_score_count) AS total_min_score_count FROM (	206	FROM users u
207	// SELECT	207	LEFT JOIN (
208	// user_id,	208	SELECT user_id, map_id, MIN(score_count) AS min_score_count
209	// MIN(score_count) AS min_score_count	209	FROM records_sp
210	// FROM records_sp	210	GROUP BY user_id, map_id
211	// GROUP BY user_id, map_id	211	) AS subquery ON subquery.user_id = u.steam_id
212	// ) AS subquery	212	WHERE u.steam_id IN (
213	// WHERE user_id = u.steam_id) AS total_min_score_count	213	SELECT user_id
214	// FROM records_sp sp	214	FROM records_sp sp
215	// JOIN users u ON u.steam_id = sp.user_id	215	JOIN maps m ON sp.map_id = m.id
216	// UNION ALL	216	JOIN games g ON m.game_id = g.id
217	// SELECT u.steam_id,	217	WHERE g.is_coop = FALSE AND m.is_disabled = FALSE
218	// (SELECT SUM(min_score_count) AS total_min_score_count FROM (	218	GROUP BY user_id
219	// SELECT	219	HAVING COUNT(DISTINCT sp.map_id) = (
220	// host_id,	220	SELECT COUNT(maps.name)
221	// partner_id,	221	FROM maps
222	// MIN(score_count) AS min_score_count	222	INNER JOIN games g ON maps.game_id = g.id
223	// FROM records_mp	223	WHERE g.is_coop = FALSE AND maps.is_disabled = FALSE
224	// GROUP BY host_id, partner_id, map_id	224	)
225	// ) AS subquery	225	)
226	// WHERE host_id = u.steam_id OR partner_id = u.steam_id) AS total_min_score_count	226	GROUP BY u.steam_id
227	// FROM records_mp mp	227	), user_mp AS (
228	// JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id	228	SELECT u.steam_id,
229	// ) AS combined_scores	229	SUM(subquery.min_score_count) AS total_min_score_count
230	// GROUP BY steam_id ORDER BY total_score ASC;`	230	FROM users u
231	// rows, err = database.DB.Query(sql)	231	LEFT JOIN (
232	// if err != nil {	232	SELECT host_id, partner_id, map_id, MIN(score_count) AS min_score_count
233	// c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))	233	FROM records_mp
234	// return	234	GROUP BY host_id, partner_id, map_id
235	// }	235	) AS subquery ON subquery.host_id = u.steam_id OR subquery.partner_id = u.steam_id
236	// placement = 1	236	WHERE u.steam_id IN (
237	// for rows.Next() {	237	SELECT host_id
238	// var steamID string	238	FROM records_mp mp
239	// var userPortalCount int	239	JOIN maps m ON mp.map_id = m.id
240	// err = rows.Scan(&steamID, &userPortalCount)	240	JOIN games g ON m.game_id = g.id
241	// if err != nil {	241	WHERE g.is_coop = TRUE AND m.is_disabled = FALSE
242	// c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))	242	GROUP BY host_id
243	// return	243	HAVING COUNT(DISTINCT mp.map_id) = (
244	// }	244	SELECT COUNT(maps.name)
245	// if completionCount != totalCount {	245	FROM maps
246	// placement++	246	INNER JOIN games g ON maps.game_id = g.id
247	// continue	247	WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
248	// }	248	)
249	// if steamID != user.(models.User).SteamID {	249	UNION
250	// placement++	250	SELECT partner_id
251	// continue	251	FROM records_mp mp
252	// }	252	JOIN maps m ON mp.map_id = m.id
253	// rankings.Cooperative.Rank = placement	253	JOIN games g ON m.game_id = g.id
254	// }	254	WHERE g.is_coop = TRUE AND m.is_disabled = FALSE
255	// }	255	GROUP BY partner_id
		256	HAVING COUNT(DISTINCT mp.map_id) = (
		257	SELECT COUNT(maps.name)
		258	FROM maps
		259	INNER JOIN games g ON maps.game_id = g.id
		260	WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
		261	)
		262	)
		263	GROUP BY u.steam_id
		264	)
		265	SELECT COALESCE(sp.steam_id, mp.steam_id) AS steam_id,
		266	COALESCE(sp.total_min_score_count, 0) + COALESCE(mp.total_min_score_count, 0) AS overall_total_min_score_count
		267	FROM user_sp sp
		268	INNER JOIN user_mp mp ON sp.steam_id = mp.steam_id
		269	ORDER BY overall_total_min_score_count ASC;`
		270	rows, err = database.DB.Query(sql)
		271	if err != nil {
		272	c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
		273	return
		274	}
		275	placement = 1
		276	for rows.Next() {
		277	var steamID string
		278	var userPortalCount int
		279	err = rows.Scan(&steamID, &userPortalCount)
		280	if err != nil {
		281	c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
		282	return
		283	}
		284	if steamID != user.(models.User).SteamID {
		285	placement++
		286	continue
		287	}
		288	rankings.Overall.Rank = placement
		289	}
		290	}
256	records := []ProfileRecords{}	291	records := []ProfileRecords{}
257	// Get singleplayer records	292	// Get singleplayer records
258	sql = `SELECT m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = sp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date	293	sql = `SELECT m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = sp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date
@@ -419,7 +454,7 @@ func FetchUser(c *gin.Context) {
419	rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount	454	rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount
420	// Get user ranking placement for singleplayer	455	// Get user ranking placement for singleplayer
421	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),	456	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),
422	(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),	457	(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),
423	(SELECT SUM(min_score_count) AS total_min_score_count FROM (	458	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
424	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)	459	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)
425	FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id GROUP BY u.steam_id, u.user_name	460	FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id GROUP BY u.steam_id, u.user_name
@@ -453,11 +488,11 @@ func FetchUser(c *gin.Context) {
453	}	488	}
454	// Get user ranking placement for multiplayer	489	// Get user ranking placement for multiplayer
455	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),	490	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),
456	(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),	491	(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),
457	(SELECT SUM(min_score_count) AS total_min_score_count FROM (	492	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
458	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)	493	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)
459	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	494	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
460	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)	495	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)
461	ORDER BY total_min_score_count ASC;`	496	ORDER BY total_min_score_count ASC;`
462	rows, err = database.DB.Query(sql)	497	rows, err = database.DB.Query(sql)
463	if err != nil {	498	if err != nil {
@@ -485,61 +520,96 @@ func FetchUser(c *gin.Context) {
485	}	520	}
486	rankings.Cooperative.Rank = placement	521	rankings.Cooperative.Rank = placement
487	}	522	}
488	// TODO: Get user ranking placement for overall if they qualify	523	// Get user ranking placement for overall if they qualify
489	// if (rankings.Singleplayer.Rank != 0) && (rankings.Cooperative.Rank != 0) {	524	if rankings.Singleplayer.Rank != 0 && rankings.Cooperative.Rank != 0 {
490	// sql = `SELECT steam_id, SUM(total_min_score_count) AS total_score	525	sql = `WITH user_sp AS (
491	// FROM (	526	SELECT u.steam_id,
492	// SELECT u.steam_id,	527	SUM(subquery.min_score_count) AS total_min_score_count
493	// (SELECT SUM(min_score_count) AS total_min_score_count FROM (	528	FROM users u
494	// SELECT	529	LEFT JOIN (
495	// user_id,	530	SELECT user_id, map_id, MIN(score_count) AS min_score_count
496	// MIN(score_count) AS min_score_count	531	FROM records_sp
497	// FROM records_sp	532	GROUP BY user_id, map_id
498	// GROUP BY user_id, map_id	533	) AS subquery ON subquery.user_id = u.steam_id
499	// ) AS subquery	534	WHERE u.steam_id IN (
500	// WHERE user_id = u.steam_id) AS total_min_score_count	535	SELECT user_id
501	// FROM records_sp sp	536	FROM records_sp sp
502	// JOIN users u ON u.steam_id = sp.user_id	537	JOIN maps m ON sp.map_id = m.id
503	// UNION ALL	538	JOIN games g ON m.game_id = g.id
504	// SELECT u.steam_id,	539	WHERE g.is_coop = FALSE AND m.is_disabled = FALSE
505	// (SELECT SUM(min_score_count) AS total_min_score_count FROM (	540	GROUP BY user_id
506	// SELECT	541	HAVING COUNT(DISTINCT sp.map_id) = (
507	// host_id,	542	SELECT COUNT(maps.name)
508	// partner_id,	543	FROM maps
509	// MIN(score_count) AS min_score_count	544	INNER JOIN games g ON maps.game_id = g.id
510	// FROM records_mp	545	WHERE g.is_coop = FALSE AND maps.is_disabled = FALSE
511	// GROUP BY host_id, partner_id, map_id	546	)
512	// ) AS subquery	547	)
513	// WHERE host_id = u.steam_id OR partner_id = u.steam_id) AS total_min_score_count	548	GROUP BY u.steam_id
514	// FROM records_mp mp	549	), user_mp AS (
515	// JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id	550	SELECT u.steam_id,
516	// ) AS combined_scores	551	SUM(subquery.min_score_count) AS total_min_score_count
517	// GROUP BY steam_id ORDER BY total_score ASC;`	552	FROM users u
518	// rows, err = database.DB.Query(sql)	553	LEFT JOIN (
519	// if err != nil {	554	SELECT host_id, partner_id, map_id, MIN(score_count) AS min_score_count
520	// c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))	555	FROM records_mp
521	// return	556	GROUP BY host_id, partner_id, map_id
522	// }	557	) AS subquery ON subquery.host_id = u.steam_id OR subquery.partner_id = u.steam_id
523	// placement = 1	558	WHERE u.steam_id IN (
524	// for rows.Next() {	559	SELECT host_id
525	// var steamID string	560	FROM records_mp mp
526	// var userPortalCount int	561	JOIN maps m ON mp.map_id = m.id
527	// err = rows.Scan(&steamID, &userPortalCount)	562	JOIN games g ON m.game_id = g.id
528	// if err != nil {	563	WHERE g.is_coop = TRUE AND m.is_disabled = FALSE
529	// c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))	564	GROUP BY host_id
530	// return	565	HAVING COUNT(DISTINCT mp.map_id) = (
531	// }	566	SELECT COUNT(maps.name)
532	// if completionCount != totalCount {	567	FROM maps
533	// placement++	568	INNER JOIN games g ON maps.game_id = g.id
534	// continue	569	WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
535	// }	570	)
536	// if steamID != user.SteamID {	571	UNION
537	// placement++	572	SELECT partner_id
538	// continue	573	FROM records_mp mp
539	// }	574	JOIN maps m ON mp.map_id = m.id
540	// rankings.Cooperative.Rank = placement	575	JOIN games g ON m.game_id = g.id
541	// }	576	WHERE g.is_coop = TRUE AND m.is_disabled = FALSE
542	// }	577	GROUP BY partner_id
		578	HAVING COUNT(DISTINCT mp.map_id) = (
		579	SELECT COUNT(maps.name)
		580	FROM maps
		581	INNER JOIN games g ON maps.game_id = g.id
		582	WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
		583	)
		584	)
		585	GROUP BY u.steam_id
		586	)
		587	SELECT COALESCE(sp.steam_id, mp.steam_id) AS steam_id,
		588	COALESCE(sp.total_min_score_count, 0) + COALESCE(mp.total_min_score_count, 0) AS overall_total_min_score_count
		589	FROM user_sp sp
		590	INNER JOIN user_mp mp ON sp.steam_id = mp.steam_id
		591	ORDER BY overall_total_min_score_count ASC;`
		592	rows, err = database.DB.Query(sql)
		593	if err != nil {
		594	c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
		595	return
		596	}
		597	placement = 1
		598	for rows.Next() {
		599	var steamID string
		600	var userPortalCount int
		601	err = rows.Scan(&steamID, &userPortalCount)
		602	if err != nil {
		603	c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
		604	return
		605	}
		606	if steamID != user.SteamID {
		607	placement++
		608	continue
		609	}
		610	rankings.Overall.Rank = placement
		611	}
		612	}
543	records := []ProfileRecords{}	613	records := []ProfileRecords{}
544	// Get singleplayer records	614	// Get singleplayer records
545	sql = `SELECT m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = sp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date	615	sql = `SELECT m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = sp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date
@@ -609,7 +679,7 @@ func FetchUser(c *gin.Context) {
609	Success: true,	679	Success: true,
610	Message: "Successfully retrieved user scores.",	680	Message: "Successfully retrieved user scores.",
611	Data: ProfileResponse{	681	Data: ProfileResponse{
612	Profile: true,	682	Profile: false,
613	SteamID: user.SteamID,	683	SteamID: user.SteamID,
614	UserName: user.UserName,	684	UserName: user.UserName,
615	AvatarLink: user.AvatarLink,	685	AvatarLink: user.AvatarLink,