From 368f6dd461d768e835124afdd5aa0228d9e0ba0b Mon Sep 17 00:00:00 2001 From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> Date: Thu, 4 Jul 2024 19:02:11 +0300 Subject: feat: update map tables db schema (#157) --- backend/database/init.sql | 15 +-- backend/database/route.sql | 283 --------------------------------------------- backend/handlers/map.go | 11 +- backend/handlers/mod.go | 86 +++----------- backend/handlers/user.go | 24 ++-- 5 files changed, 33 insertions(+), 386 deletions(-) delete mode 100644 backend/database/route.sql (limited to 'backend') diff --git a/backend/database/init.sql b/backend/database/init.sql index 1fda15d..c33821b 100644 --- a/backend/database/init.sql +++ b/backend/database/init.sql @@ -48,25 +48,14 @@ CREATE TABLE maps ( FOREIGN KEY (chapter_id) REFERENCES chapters(id) ); -CREATE TABLE map_routes ( - id SERIAL, - map_id SMALLINT NOT NULL, - category_id SMALLINT NOT NULL, - score_count SMALLINT NOT NULL, - description TEXT NOT NULL, - showcase TEXT NOT NULL DEFAULT, - PRIMARY KEY (id), - FOREIGN KEY (map_id) REFERENCES maps(id), - FOREIGN KEY (category_id) REFERENCES categories(id), - UNIQUE (map_id, category_id, score_count) -); - CREATE TABLE map_history ( id SERIAL, map_id SMALLINT NOT NULL, category_id SMALLINT NOT NULL, user_name TEXT NOT NULL, score_count SMALLINT NOT NULL, + description TEXT NOT NULL, + showcase TEXT NOT NULL DEFAULT, record_date DATE NOT NULL, PRIMARY KEY (id), FOREIGN KEY (category_id) REFERENCES categories(id), diff --git a/backend/database/route.sql b/backend/database/route.sql deleted file mode 100644 index 8b23a8c..0000000 --- a/backend/database/route.sql +++ /dev/null @@ -1,283 +0,0 @@ -INSERT INTO map_routes(map_id,category_id,score_count,description,showcase) VALUES --- Portal 2 Singleplayer --- 1 -(3,1,3,'',''), -(3,1,1,'',''), -(3,1,0,'',''), -(4,1,1,'',''), -(4,1,0,'',''), -(5,1,2,'',''), -(5,1,1,'',''), -(6,1,4,'',''), -(6,1,3,'',''), -(6,1,2,'',''), -(7,1,0,'',''), -(9,1,4,'',''), -(9,1,3,'',''), -(9,1,2,'',''), -(9,1,0,'',''), --- 2 -(10,1,2,'',''), -(10,1,0,'',''), -(11,1,2,'',''), -(11,1,0,'',''), -(12,1,2,'',''), -(13,1,3,'',''), -(13,1,2,'',''), -(13,1,0,'',''), -(14,1,0,'',''), -(15,1,2,'',''), -(16,1,2,'',''), -(16,1,0,'',''), -(17,1,2,'',''), -(17,1,0,'',''), --- 3 -(18,1,5,'',''), -(18,1,4,'',''), -(18,1,3,'',''), -(18,1,2,'',''), -(18,1,0,'',''), -(19,1,2,'',''), -(20,1,5,'',''), -(20,1,4,'',''), -(20,1,3,'',''), -(20,1,2,'',''), -(21,1,4,'',''), -(21,1,2,'',''), -(21,1,0,'',''), -(22,1,0,'',''), -(23,1,2,'',''), -(23,1,0,'',''), -(24,1,0,'',''), -(25,1,0,'',''), -(26,1,3,'',''), -(26,1,2,'',''), -(26,1,0,'',''), --- 4 -(27,1,2,'',''), -(27,1,0,'',''), -(28,1,7,'',''), -(28,1,2,'',''), -(28,1,0,'',''), -(29,1,0,'',''), -(30,1,2,'',''), -(31,1,0,'',''), --- 5 -(32,1,6,'',''), -(32,1,5,'',''), -(33,1,7,'',''), -(33,1,5,'',''), -(33,1,4,'',''), -(34,1,3,'',''), -(34,1,2,'',''), -(34,1,0,'',''), -(35,1,2,'',''), --- 6 -(36,1,6,'',''), -(36,1,5,'',''), -(36,1,4,'',''), -(36,1,2,'',''), -(37,1,7,'',''), -(37,1,6,'',''), -(37,1,5,'',''), -(37,1,4,'',''), -(38,1,2,'',''), -(38,1,0,'',''), -(39,1,6,'',''), -(39,1,5,'',''), -(39,1,4,'',''), -(39,1,3,'',''), -(40,1,7,'',''), -(40,1,6,'',''), -(40,1,4,'',''), -(40,1,3,'',''), -(40,1,2,'',''), -(40,1,0,'',''), -(41,1,7,'',''), -(41,1,6,'',''), -(41,1,5,'',''), --- 7 -(42,1,4,'',''), -(42,1,2,'',''), -(43,1,5,'',''), -(43,1,2,'',''), -(43,1,0,'',''), -(44,1,18,'',''), -(44,1,13,'',''), -(44,1,12,'',''), -(44,1,11,'',''), -(44,1,9,'',''), -(45,1,23,'',''), -(45,1,22,'',''), -(45,1,17,'',''), -(45,1,16,'',''), -(45,1,15,'',''), -(45,1,12,'',''), -(45,1,10,'',''), -(45,1,7,'',''), -(45,1,4,'',''), --- 8 -(46,1,6,'',''), -(46,1,2,'',''), -(47,1,2,'',''), -(47,1,0,'',''), -(48,1,5,'',''), -(48,1,2,'',''), -(48,1,0,'',''), -(49,1,4,'',''), -(49,1,2,'',''), -(49,1,0,'',''), -(50,1,4,'',''), -(50,1,2,'',''), -(50,1,0,'',''), -(51,1,3,'',''), -(51,1,2,'',''), -(52,1,0,'',''), -(53,1,9,'',''), -(53,1,2,'',''), -(53,1,0,'',''), -(54,1,7,'',''), -(54,1,6,'',''), -(54,1,5,'',''), -(54,1,4,'',''), -(54,1,3,'',''), -(55,1,7,'',''), -(55,1,3,'',''), -(55,1,2,'',''), -(55,1,0,'',''), -(56,1,9,'',''), -(56,1,5,'',''), -(56,1,4,'',''), -(56,1,2,'',''), --- 9 -(57,1,7,'',''), -(57,1,5,'',''), -(57,1,0,'',''), -(58,1,2,'',''), -(59,1,7,'',''), -(59,1,6,'',''), -(60,1,7,'',''), -(60,1,6,'',''), --- Portal 2 Cooperative --- 1 -(63,1,0,'',''), -(64,1,3,'',''), -(64,1,2,'',''), -(65,1,4,'',''), -(65,1,3,'',''), -(65,1,2,'',''), -(66,1,3,'',''), -(66,1,2,'',''), -(67,1,0,'',''), -(68,1,0,'',''), --- 2 -(69,1,4,'',''), -(70,1,6,'',''), -(70,1,4,'',''), -(70,1,2,'',''), -(70,1,0,'',''), -(71,1,3,'',''), -(71,1,0,'',''), -(72,1,4,'',''), -(72,1,2,'',''), -(73,1,9,'',''), -(73,1,8,'',''), -(73,1,7,'',''), -(73,1,6,'',''), -(73,1,4,'',''), -(74,1,5,'',''), -(74,1,7,'',''), -(74,1,3,'',''), -(74,1,2,'',''), -(75,1,5,'',''), -(75,1,4,'',''), -(75,1,2,'',''), -(75,1,0,'',''), -(76,1,3,'',''), -(76,1,0,'',''), --- 3 -(77,1,3,'',''), -(78,1,4,'',''), -(78,1,3,'',''), -(78,1,2,'',''), -(79,1,5,'',''), -(79,1,4,'',''), -(79,1,2,'',''), -(79,1,0,'',''), -(80,1,5,'',''), -(80,1,4,'',''), -(81,1,7,'',''), -(81,1,6,'',''), -(81,1,5,'',''), -(81,1,4,'',''), -(82,1,4,'',''), -(83,1,5,'',''), -(83,1,2,'',''), -(83,1,0,'',''), -(84,1,6,'',''), -(84,1,4,'',''), -(84,1,2,'',''), -(84,1,0,'',''), --- 4 -(85,1,3,'',''), -(85,1,0,'',''), -(86,1,3,'',''), -(86,1,0,'',''), -(87,1,3,'',''), -(87,1,2,'',''), -(87,1,0,'',''), -(88,1,4,'',''), -(88,1,0,'',''), -(89,1,0,'',''), -(90,1,4,'',''), -(90,1,2,'',''), -(90,1,0,'',''), -(91,1,2,'',''), -(91,1,0,'',''), -(92,1,5,'',''), -(92,1,4,'',''), -(92,1,2,'',''), -(92,1,0,'',''), -(93,1,2,'',''), -(93,1,0,'',''), --- 5 -(94,1,2,'',''), -(94,1,0,'',''), -(95,1,2,'',''), -(96,1,4,'',''), -(96,1,2,'',''), -(97,1,7,'',''), -(97,1,4,'',''), -(97,1,2,'',''), -(98,1,0,'',''), -(99,1,3,'',''), -(99,1,2,'',''), -(99,1,0,'',''), -(100,1,0,'',''), -(101,1,2,'',''), -(101,1,0,'',''), --- 6 -(102,1,3,'',''), -(102,1,2,'',''), -(102,1,0,'',''), -(103,1,0,'',''), -(104,1,0,'',''), -(105,1,8,'',''), -(105,1,5,'',''), -(105,1,4,'',''), -(105,1,3,'',''), -(105,1,2,'',''), -(106,1,4,'',''), -(106,1,3,'',''), -(106,1,0,'',''), -(107,1,2,'',''), -(107,1,0,'',''), -(108,1,0,'',''), -(109,1,5,'',''), -(109,1,0,'',''), -(110,1,15,'',''), -(110,1,6,'',''), -(110,1,5,'',''), -(110,1,4,'',''), -(110,1,2,'',''), -(110,1,0,'',''); \ No newline at end of file diff --git a/backend/handlers/map.go b/backend/handlers/map.go index f2ea8ac..bf7c821 100644 --- a/backend/handlers/map.go +++ b/backend/handlers/map.go @@ -87,12 +87,11 @@ func FetchMapSummary(c *gin.Context) { return } // Get map routes and histories - sql = `SELECT r.id, c.id, c.name, h.user_name, h.score_count, h.record_date, r.description, r.showcase, COALESCE(avg(rating), 0.0) FROM map_routes r - INNER JOIN categories c ON r.category_id = c.id - INNER JOIN map_history h ON r.map_id = h.map_id AND r.category_id = h.category_id - LEFT JOIN map_ratings rt ON r.map_id = rt.map_id AND r.category_id = rt.category_id - WHERE r.map_id = $1 AND h.score_count = r.score_count GROUP BY r.id, c.id, h.user_name, h.score_count, h.record_date, r.description, r.showcase - ORDER BY h.record_date ASC;` + sql = `SELECT mh.id, c.id, c.name, mh.user_name, mh.score_count, mh.record_date, mh.description, mh.showcase, COALESCE(avg(rating), 0.0) FROM map_history mh + INNER JOIN categories c ON mh.category_id = c.id + LEFT JOIN map_ratings rt ON mh.map_id = rt.map_id AND mh.category_id = rt.category_id + WHERE mh.map_id = $1 AND mh.score_count = mh.score_count GROUP BY mh.id, c.id, mh.user_name, mh.score_count, mh.record_date, mh.description, mh.showcase + ORDER BY mh.record_date ASC;` rows, err := database.DB.Query(sql, id) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) diff --git a/backend/handlers/mod.go b/backend/handlers/mod.go index 3709e1d..72a9fd8 100644 --- a/backend/handlers/mod.go +++ b/backend/handlers/mod.go @@ -16,7 +16,7 @@ type CreateMapSummaryRequest struct { Description string `json:"description" binding:"required"` Showcase string `json:"showcase"` UserName string `json:"user_name" binding:"required"` - ScoreCount *int `json:"score_count"` + ScoreCount int `json:"score_count" binding:"required"` RecordDate time.Time `json:"record_date" binding:"required"` } @@ -25,7 +25,7 @@ type EditMapSummaryRequest struct { Description string `json:"description" binding:"required"` Showcase string `json:"showcase"` UserName string `json:"user_name" binding:"required"` - ScoreCount int `json:"score_count"` + ScoreCount int `json:"score_count" binding:"required"` RecordDate time.Time `json:"record_date" binding:"required"` } @@ -93,17 +93,9 @@ func CreateMapSummary(c *gin.Context) { return } // Update database with new data - sql = `INSERT INTO map_routes (map_id,category_id,score_count,description,showcase) + sql = `INSERT INTO map_history (map_id,category_id,user_name,score_count,description,showcase,record_date) VALUES ($1,$2,$3,$4,$5)` - _, err = tx.Exec(sql, mapID, request.CategoryID, request.ScoreCount, request.Description, request.Showcase) - if err != nil { - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryCreateFail, fmt.Sprintf("INSERT#map_routes: %s", err.Error())) - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return - } - sql = `INSERT INTO map_history (map_id,category_id,user_name,score_count,record_date) - VALUES ($1,$2,$3,$4,$5)` - _, err = tx.Exec(sql, mapID, request.CategoryID, request.UserName, request.ScoreCount, request.RecordDate) + _, err = tx.Exec(sql, mapID, request.CategoryID, request.UserName, request.ScoreCount, request.Description, request.Showcase, request.RecordDate) if err != nil { CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryCreateFail, fmt.Sprintf("INSERT#map_history: %s", err.Error())) c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) @@ -113,7 +105,7 @@ func CreateMapSummary(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryCreateSuccess, fmt.Sprintf("MapID: %d | CategoryID: %d | ScoreCount: %d", mapID, request.CategoryID, *request.ScoreCount)) + CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryCreateSuccess, fmt.Sprintf("MapID: %d | CategoryID: %d | ScoreCount: %d", mapID, request.CategoryID, request.ScoreCount)) c.JSON(http.StatusOK, models.Response{ Success: true, Message: "Successfully created map summary.", @@ -145,7 +137,8 @@ func EditMapSummary(c *gin.Context) { } // Bind parameter and body id := c.Param("mapid") - mapID, err := strconv.Atoi(id) + // we get mapid in path parameters, but it's not really used anywhere here lol. + _, err := strconv.Atoi(id) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return @@ -163,34 +156,11 @@ func EditMapSummary(c *gin.Context) { return } defer tx.Rollback() - // Fetch route category and score count - var categoryID, scoreCount, historyID int - sql := `SELECT mr.category_id, mr.score_count FROM map_routes mr INNER JOIN maps m ON m.id = mr.map_id WHERE m.id = $1 AND mr.id = $2` - err = database.DB.QueryRow(sql, mapID, request.RouteID).Scan(&categoryID, &scoreCount) - if err != nil { - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditFail, fmt.Sprintf("(RouteID: %d) SELECT#map_routes: %s", request.RouteID, err.Error())) - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return - } - sql = `SELECT mh.id FROM map_history mh WHERE mh.score_count = $1 AND mh.category_id = $2 AND mh.map_id = $3` - err = database.DB.QueryRow(sql, scoreCount, categoryID, mapID).Scan(&historyID) - if err != nil { - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditFail, fmt.Sprintf("(RouteID: %d) SELECT#map_history: %s", request.RouteID, err.Error())) - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return - } // Update database with new data - sql = `UPDATE map_routes SET score_count = $2, description = $3, showcase = $4 WHERE id = $1` - _, err = tx.Exec(sql, request.RouteID, request.ScoreCount, request.Description, request.Showcase) - if err != nil { - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditFail, fmt.Sprintf("(RouteID: %d) UPDATE#map_routes: %s", request.RouteID, err.Error())) - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return - } - sql = `UPDATE map_history SET user_name = $2, score_count = $3, record_date = $4 WHERE id = $1` - _, err = tx.Exec(sql, historyID, request.UserName, request.ScoreCount, request.RecordDate) + sql := `UPDATE map_history SET user_name = $2, score_count = $3, record_date = $4, description = $5, showcase = $6 WHERE id = $1` + _, err = tx.Exec(sql, request.RouteID, request.UserName, request.ScoreCount, request.RecordDate, request.Description, request.Showcase) if err != nil { - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditFail, fmt.Sprintf("(HistoryID: %d) UPDATE#map_history: %s", historyID, err.Error())) + CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditFail, fmt.Sprintf("(HistoryID: %d) UPDATE#map_history: %s", request.RouteID, err.Error())) c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } @@ -198,7 +168,6 @@ func EditMapSummary(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditSuccess, fmt.Sprintf("MapID: %d | CategoryID: %d | ScoreCount: %d", mapID, categoryID, scoreCount)) c.JSON(http.StatusOK, models.Response{ Success: true, Message: "Successfully updated map summary.", @@ -230,7 +199,8 @@ func DeleteMapSummary(c *gin.Context) { } // Bind parameter and body id := c.Param("mapid") - mapID, err := strconv.Atoi(id) + // we get mapid in path parameters, but it's not really used anywhere here lol. + _, err := strconv.Atoi(id) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return @@ -248,38 +218,11 @@ func DeleteMapSummary(c *gin.Context) { return } defer tx.Rollback() - // Fetch route category and score count - var checkMapID, scoreCount, categoryID, mapHistoryID int - sql := `SELECT m.id, mr.score_count, mr.category_id FROM maps m INNER JOIN map_routes mr ON m.id=mr.map_id WHERE m.id = $1 AND mr.id = $2` - err = database.DB.QueryRow(sql, mapID, request.RouteID).Scan(&checkMapID, &scoreCount, &categoryID) - if err != nil { - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteFail, fmt.Sprintf("(RouteID: %d) SELECT#map_routes: %s", request.RouteID, err.Error())) - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return - } - if mapID != checkMapID { - c.JSON(http.StatusOK, models.ErrorResponse("Map ID does not exist.")) - return - } - sql = `SELECT mh.id FROM maps m INNER JOIN map_routes mr ON m.id=mr.map_id INNER JOIN map_history mh ON m.id=mh.map_id WHERE m.id = $1 AND mh.category_id = $2 AND mh.score_count = $3` - err = database.DB.QueryRow(sql, mapID, categoryID, scoreCount).Scan(&mapHistoryID) - if err != nil { - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteFail, fmt.Sprintf("(RouteID: %d) SELECT#map_history: %s", request.RouteID, err.Error())) - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return - } // Update database with new data - sql = `DELETE FROM map_routes mr WHERE mr.id = $1 ` + sql := `DELETE FROM map_history mh WHERE mh.id = $1` _, err = tx.Exec(sql, request.RouteID) if err != nil { - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteFail, fmt.Sprintf("(RouteID: %d) DELETE#map_routes: %s", request.RouteID, err.Error())) - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return - } - sql = `DELETE FROM map_history mh WHERE mh.id = $1` - _, err = tx.Exec(sql, mapHistoryID) - if err != nil { - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteFail, fmt.Sprintf("(HistoryID: %d) DELETE#map_history: %s", mapHistoryID, err.Error())) + CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteFail, fmt.Sprintf("(HistoryID: %d) DELETE#map_history: %s", request.RouteID, err.Error())) c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } @@ -287,7 +230,6 @@ func DeleteMapSummary(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteSuccess, fmt.Sprintf("MapID: %d | CategoryID: %d | ScoreCount: %d", mapID, categoryID, scoreCount)) c.JSON(http.StatusOK, models.Response{ Success: true, Message: "Successfully delete map summary.", diff --git a/backend/handlers/user.go b/backend/handlers/user.go index 88bbe45..8febf3a 100644 --- a/backend/handlers/user.go +++ b/backend/handlers/user.go @@ -99,15 +99,15 @@ func Profile(c *gin.Context) { // Get user completion count sql = `SELECT 'records_sp' AS table_name, COUNT(sp.id) FROM records_sp sp JOIN ( - SELECT mr.map_id, MIN(mr.score_count) AS min_score_count - FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id + SELECT mh.map_id, MIN(mh.score_count) AS min_score_count + FROM public.map_history mh WHERE mh.category_id = 1 GROUP BY mh.map_id ) AS subquery_sp ON sp.map_id = subquery_sp.map_id AND sp.score_count = subquery_sp.min_score_count WHERE sp.user_id = $1 AND sp.is_deleted = false UNION ALL SELECT 'records_mp' AS table_name, COUNT(mp.id) FROM public.records_mp mp JOIN ( - SELECT mr.map_id, MIN(mr.score_count) AS min_score_count - FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id + SELECT mh.map_id, MIN(mh.score_count) AS min_score_count + FROM public.map_history mh WHERE mh.category_id = 1 GROUP BY mh.map_id ) AS subquery_mp ON mp.map_id = subquery_mp.map_id AND mp.score_count = subquery_mp.min_score_count WHERE (mp.host_id = $1 OR mp.partner_id = $1) AND mp.is_deleted = false` rows, err := database.DB.Query(sql, user.(models.User).SteamID) @@ -293,7 +293,7 @@ func Profile(c *gin.Context) { } records := []ProfileRecords{} // Get singleplayer records - sql = `SELECT sp.id, 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 + sql = `SELECT sp.id, m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mh.score_count FROM map_history mh WHERE mh.map_id = sp.map_id ORDER BY mh.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date FROM records_sp sp INNER JOIN maps m ON sp.map_id = m.id WHERE sp.user_id = $1 AND sp.is_deleted = false ORDER BY sp.map_id, sp.score_count, sp.score_time` rows, err = database.DB.Query(sql, user.(models.User).SteamID) if err != nil { @@ -347,7 +347,7 @@ func Profile(c *gin.Context) { records[len(records)-1].Scores = append(records[len(records)-1].Scores, score) } // Get multiplayer records - sql = `SELECT mp.id, m.game_id, m.chapter_id, mp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = mp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count, mp.score_count, mp.score_time, CASE WHEN host_id = $1 THEN mp.host_demo_id WHEN partner_id = $1 THEN mp.partner_demo_id END demo_id, mp.record_date + sql = `SELECT mp.id, m.game_id, m.chapter_id, mp.map_id, m."name", (SELECT mh.score_count FROM map_history mh WHERE mh.map_id = mp.map_id ORDER BY mh.score_count ASC LIMIT 1) AS wr_count, mp.score_count, mp.score_time, CASE WHEN host_id = $1 THEN mp.host_demo_id WHEN partner_id = $1 THEN mp.partner_demo_id END demo_id, mp.record_date FROM records_mp mp INNER JOIN maps m ON mp.map_id = m.id WHERE (mp.host_id = $1 OR mp.partner_id = $1) AND mp.is_deleted = false ORDER BY mp.map_id, mp.score_count, mp.score_time` rows, err = database.DB.Query(sql, user.(models.User).SteamID) if err != nil { @@ -473,15 +473,15 @@ func FetchUser(c *gin.Context) { // Get user completion count sql = `SELECT 'records_sp' AS table_name, COUNT(sp.id) FROM records_sp sp JOIN ( - SELECT mr.map_id, MIN(mr.score_count) AS min_score_count - FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id + SELECT mh.map_id, MIN(mh.score_count) AS min_score_count + FROM public.map_history mh WHERE mh.category_id = 1 GROUP BY mh.map_id ) AS subquery_sp ON sp.map_id = subquery_sp.map_id AND sp.score_count = subquery_sp.min_score_count WHERE sp.user_id = $1 AND sp.is_deleted = false UNION ALL SELECT 'records_mp' AS table_name, COUNT(mp.id) FROM public.records_mp mp JOIN ( - SELECT mr.map_id, MIN(mr.score_count) AS min_score_count - FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id + SELECT mh.map_id, MIN(mh.score_count) AS min_score_count + FROM public.map_history mh WHERE mh.category_id = 1 GROUP BY mh.map_id ) AS subquery_mp ON mp.map_id = subquery_mp.map_id AND mp.score_count = subquery_mp.min_score_count WHERE (mp.host_id = $1 OR mp.partner_id = $1) AND mp.is_deleted = false` rows, err = database.DB.Query(sql, user.SteamID) @@ -667,7 +667,7 @@ func FetchUser(c *gin.Context) { } records := []ProfileRecords{} // Get singleplayer records - sql = `SELECT sp.id, 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 + sql = `SELECT sp.id, m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mh.score_count FROM map_history mh WHERE mh.map_id = sp.map_id ORDER BY mh.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date FROM records_sp sp INNER JOIN maps m ON sp.map_id = m.id WHERE sp.user_id = $1 AND sp.is_deleted = false ORDER BY sp.map_id, sp.score_count, sp.score_time` rows, err = database.DB.Query(sql, user.SteamID) if err != nil { @@ -721,7 +721,7 @@ func FetchUser(c *gin.Context) { records[len(records)-1].Scores = append(records[len(records)-1].Scores, score) } // Get multiplayer records - sql = `SELECT mp.id, m.game_id, m.chapter_id, mp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = mp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count, mp.score_count, mp.score_time, CASE WHEN host_id = $1 THEN mp.host_demo_id WHEN partner_id = $1 THEN mp.partner_demo_id END demo_id, mp.record_date + sql = `SELECT mp.id, m.game_id, m.chapter_id, mp.map_id, m."name", (SELECT mh.score_count FROM map_history mh WHERE mh.map_id = mp.map_id ORDER BY mh.score_count ASC LIMIT 1) AS wr_count, mp.score_count, mp.score_time, CASE WHEN host_id = $1 THEN mp.host_demo_id WHEN partner_id = $1 THEN mp.partner_demo_id END demo_id, mp.record_date FROM records_mp mp INNER JOIN maps m ON mp.map_id = m.id WHERE (mp.host_id = $1 OR mp.partner_id = $1) AND mp.is_deleted = false ORDER BY mp.map_id, mp.score_count, mp.score_time` rows, err = database.DB.Query(sql, user.SteamID) if err != nil { -- cgit v1.2.3