From 483dd529f156c9ae41405153c1df0a19cc1c66c8 Mon Sep 17 00:00:00 2001 From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> Date: Thu, 24 Oct 2024 11:16:09 +0300 Subject: backend: fix wr deltas --- backend/handlers/user.go | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'backend/handlers/user.go') diff --git a/backend/handlers/user.go b/backend/handlers/user.go index 17a7819..ea96732 100644 --- a/backend/handlers/user.go +++ b/backend/handlers/user.go @@ -350,7 +350,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 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 + 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 AND mh.category_id = 1 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 { @@ -672,7 +672,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 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 + 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 AND mh.category_id = 1 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 { @@ -728,7 +728,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 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 + 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 AND mh.category_id = 1 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 From c987c7605784b720c2dc6e0f77a4df48715bc6a2 Mon Sep 17 00:00:00 2001 From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> Date: Thu, 24 Oct 2024 11:41:32 +0300 Subject: backend: fix user completion count --- backend/handlers/user.go | 48 ++++++++++++++++++++++++------------------------ 1 file changed, 24 insertions(+), 24 deletions(-) (limited to 'backend/handlers/user.go') diff --git a/backend/handlers/user.go b/backend/handlers/user.go index ea96732..dc058c8 100644 --- a/backend/handlers/user.go +++ b/backend/handlers/user.go @@ -98,19 +98,19 @@ func Profile(c *gin.Context) { } rankings.Overall.CompletionTotal = rankings.Singleplayer.CompletionTotal + rankings.Cooperative.CompletionTotal // Get user completion count - sql = `SELECT 'records_sp' AS table_name, COUNT(sp.id) - FROM records_sp sp JOIN ( - 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 + sql = `SELECT 'records_sp' AS table_name, COUNT(*) FROM ( + SELECT sp.map_id FROM records_sp sp JOIN ( + SELECT mh.map_id, MIN(mh.score_count) AS min_score_count FROM 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 GROUP BY sp.map_id + ) AS unique_maps UNION ALL - SELECT 'records_mp' AS table_name, COUNT(mp.id) - FROM public.records_mp mp JOIN ( - 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` + SELECT 'records_mp' AS table_name, COUNT(*) FROM ( + SELECT mp.map_id FROM records_mp mp JOIN ( + SELECT mh.map_id, MIN(mh.score_count) AS min_score_count FROM 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 GROUP BY mp.map_id + ) AS unique_maps` rows, err := database.DB.Query(sql, user.(models.User).SteamID) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) @@ -476,19 +476,19 @@ func FetchUser(c *gin.Context) { } rankings.Overall.CompletionTotal = rankings.Singleplayer.CompletionTotal + rankings.Cooperative.CompletionTotal // Get user completion count - sql = `SELECT 'records_sp' AS table_name, COUNT(sp.id) - FROM records_sp sp JOIN ( - 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 + sql = `SELECT 'records_sp' AS table_name, COUNT(*) FROM ( + SELECT sp.map_id FROM records_sp sp JOIN ( + SELECT mh.map_id, MIN(mh.score_count) AS min_score_count FROM 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 GROUP BY sp.map_id + ) AS unique_maps UNION ALL - SELECT 'records_mp' AS table_name, COUNT(mp.id) - FROM public.records_mp mp JOIN ( - 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` + SELECT 'records_mp' AS table_name, COUNT(*) FROM ( + SELECT mp.map_id FROM records_mp mp JOIN ( + SELECT mh.map_id, MIN(mh.score_count) AS min_score_count FROM 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 GROUP BY mp.map_id + ) AS unique_maps` rows, err = database.DB.Query(sql, user.SteamID) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) -- cgit v1.2.3