aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--backend/handlers/home.go48
-rw-r--r--backend/handlers/map.go2
-rw-r--r--backend/handlers/user.go304
-rw-r--r--backend/models/models.go7
-rw-r--r--docs/docs.go7
-rw-r--r--docs/swagger.json7
-rw-r--r--docs/swagger.yaml6
7 files changed, 249 insertions, 132 deletions
diff --git a/backend/handlers/home.go b/backend/handlers/home.go
index 5863218..16ac993 100644
--- a/backend/handlers/home.go
+++ b/backend/handlers/home.go
@@ -43,8 +43,8 @@ func Rankings(c *gin.Context) {
43 Multiplayer: []models.UserRanking{}, 43 Multiplayer: []models.UserRanking{},
44 } 44 }
45 // Singleplayer rankings 45 // Singleplayer rankings
46 sql := `SELECT u.steam_id, u.user_name, COUNT(DISTINCT map_id), 46 sql := `SELECT u.steam_id, u.user_name, u.avatar_link, COUNT(DISTINCT map_id),
47 (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), 47 (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),
48 (SELECT SUM(min_score_count) AS total_min_score_count FROM ( 48 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
49 SELECT 49 SELECT
50 user_id, 50 user_id,
@@ -63,7 +63,7 @@ func Rankings(c *gin.Context) {
63 ranking := models.UserRanking{} 63 ranking := models.UserRanking{}
64 var currentCount int 64 var currentCount int
65 var totalCount int 65 var totalCount int
66 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &currentCount, &totalCount, &ranking.TotalScore) 66 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &currentCount, &totalCount, &ranking.TotalScore)
67 if err != nil { 67 if err != nil {
68 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 68 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
69 return 69 return
@@ -74,8 +74,8 @@ func Rankings(c *gin.Context) {
74 response.Singleplayer = append(response.Singleplayer, ranking) 74 response.Singleplayer = append(response.Singleplayer, ranking)
75 } 75 }
76 // Multiplayer rankings 76 // Multiplayer rankings
77 sql = `SELECT u.steam_id, u.user_name, COUNT(DISTINCT map_id), 77 sql = `SELECT u.steam_id, u.user_name, u.avatar_link, COUNT(DISTINCT map_id),
78 (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), 78 (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),
79 (SELECT SUM(min_score_count) AS total_min_score_count FROM ( 79 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
80 SELECT 80 SELECT
81 host_id, 81 host_id,
@@ -95,7 +95,7 @@ func Rankings(c *gin.Context) {
95 ranking := models.UserRanking{} 95 ranking := models.UserRanking{}
96 var currentCount int 96 var currentCount int
97 var totalCount int 97 var totalCount int
98 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &currentCount, &totalCount, &ranking.TotalScore) 98 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &currentCount, &totalCount, &ranking.TotalScore)
99 if err != nil { 99 if err != nil {
100 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 100 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
101 return 101 return
@@ -121,12 +121,48 @@ func Rankings(c *gin.Context) {
121 sort.Slice(response.Singleplayer, func(i, j int) bool { 121 sort.Slice(response.Singleplayer, func(i, j int) bool {
122 return response.Singleplayer[i].TotalScore < response.Singleplayer[j].TotalScore 122 return response.Singleplayer[i].TotalScore < response.Singleplayer[j].TotalScore
123 }) 123 })
124 placement := 1
125 ties := 0
126 for index := 0; index < len(response.Singleplayer); index++ {
127 if index != 0 && response.Singleplayer[index-1].TotalScore == response.Singleplayer[index].TotalScore {
128 ties++
129 response.Singleplayer[index].Placement = placement - ties
130 } else {
131 ties = 0
132 response.Singleplayer[index].Placement = placement
133 }
134 placement++
135 }
124 sort.Slice(response.Multiplayer, func(i, j int) bool { 136 sort.Slice(response.Multiplayer, func(i, j int) bool {
125 return response.Multiplayer[i].TotalScore < response.Multiplayer[j].TotalScore 137 return response.Multiplayer[i].TotalScore < response.Multiplayer[j].TotalScore
126 }) 138 })
139 placement = 1
140 ties = 0
141 for index := 0; index < len(response.Multiplayer); index++ {
142 if index != 0 && response.Multiplayer[index-1].TotalScore == response.Multiplayer[index].TotalScore {
143 ties++
144 response.Multiplayer[index].Placement = placement - ties
145 } else {
146 ties = 0
147 response.Multiplayer[index].Placement = placement
148 }
149 placement++
150 }
127 sort.Slice(response.Overall, func(i, j int) bool { 151 sort.Slice(response.Overall, func(i, j int) bool {
128 return response.Overall[i].TotalScore < response.Overall[j].TotalScore 152 return response.Overall[i].TotalScore < response.Overall[j].TotalScore
129 }) 153 })
154 placement = 1
155 ties = 0
156 for index := 0; index < len(response.Overall); index++ {
157 if index != 0 && response.Overall[index-1].TotalScore == response.Overall[index].TotalScore {
158 ties++
159 response.Overall[index].Placement = placement - ties
160 } else {
161 ties = 0
162 response.Overall[index].Placement = placement
163 }
164 placement++
165 }
130 c.JSON(http.StatusOK, models.Response{ 166 c.JSON(http.StatusOK, models.Response{
131 Success: true, 167 Success: true,
132 Message: "Successfully retrieved rankings.", 168 Message: "Successfully retrieved rankings.",
diff --git a/backend/handlers/map.go b/backend/handlers/map.go
index faccee4..c0776d1 100644
--- a/backend/handlers/map.go
+++ b/backend/handlers/map.go
@@ -228,6 +228,7 @@ func FetchMapLeaderboards(c *gin.Context) {
228 ties++ 228 ties++
229 record.Placement = placement - ties 229 record.Placement = placement - ties
230 } else { 230 } else {
231 ties = 0
231 record.Placement = placement 232 record.Placement = placement
232 } 233 }
233 records = append(records, record) 234 records = append(records, record)
@@ -278,6 +279,7 @@ func FetchMapLeaderboards(c *gin.Context) {
278 ties++ 279 ties++
279 record.Placement = placement - ties 280 record.Placement = placement - ties
280 } else { 281 } else {
282 ties = 0
281 record.Placement = placement 283 record.Placement = placement
282 } 284 }
283 records = append(records, record) 285 records = append(records, record)
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,
diff --git a/backend/models/models.go b/backend/models/models.go
index e5d92fb..8e16c1c 100644
--- a/backend/models/models.go
+++ b/backend/models/models.go
@@ -78,8 +78,9 @@ type MapRecords struct {
78} 78}
79 79
80type UserRanking struct { 80type UserRanking struct {
81 User UserShort `json:"user"` 81 Placement int `json:"placement"`
82 TotalScore int `json:"total_score"` 82 User UserShortWithAvatar `json:"user"`
83 TotalScore int `json:"total_score"`
83} 84}
84 85
85type Game struct { 86type Game struct {
@@ -105,7 +106,7 @@ type Title struct {
105 106
106type Links struct { 107type Links struct {
107 P2SR string `json:"p2sr"` 108 P2SR string `json:"p2sr"`
108 Steam string `json:"stream"` 109 Steam string `json:"steam"`
109 YouTube string `json:"youtube"` 110 YouTube string `json:"youtube"`
110 Twitch string `json:"twitch"` 111 Twitch string `json:"twitch"`
111} 112}
diff --git a/docs/docs.go b/docs/docs.go
index 5a107c1..bb2738c 100644
--- a/docs/docs.go
+++ b/docs/docs.go
@@ -1794,7 +1794,7 @@ const docTemplate = `{
1794 "p2sr": { 1794 "p2sr": {
1795 "type": "string" 1795 "type": "string"
1796 }, 1796 },
1797 "stream": { 1797 "steam": {
1798 "type": "string" 1798 "type": "string"
1799 }, 1799 },
1800 "twitch": { 1800 "twitch": {
@@ -1933,11 +1933,14 @@ const docTemplate = `{
1933 "models.UserRanking": { 1933 "models.UserRanking": {
1934 "type": "object", 1934 "type": "object",
1935 "properties": { 1935 "properties": {
1936 "placement": {
1937 "type": "integer"
1938 },
1936 "total_score": { 1939 "total_score": {
1937 "type": "integer" 1940 "type": "integer"
1938 }, 1941 },
1939 "user": { 1942 "user": {
1940 "$ref": "#/definitions/models.UserShort" 1943 "$ref": "#/definitions/models.UserShortWithAvatar"
1941 } 1944 }
1942 } 1945 }
1943 }, 1946 },
diff --git a/docs/swagger.json b/docs/swagger.json
index d118ce7..ffc30a9 100644
--- a/docs/swagger.json
+++ b/docs/swagger.json
@@ -1787,7 +1787,7 @@
1787 "p2sr": { 1787 "p2sr": {
1788 "type": "string" 1788 "type": "string"
1789 }, 1789 },
1790 "stream": { 1790 "steam": {
1791 "type": "string" 1791 "type": "string"
1792 }, 1792 },
1793 "twitch": { 1793 "twitch": {
@@ -1926,11 +1926,14 @@
1926 "models.UserRanking": { 1926 "models.UserRanking": {
1927 "type": "object", 1927 "type": "object",
1928 "properties": { 1928 "properties": {
1929 "placement": {
1930 "type": "integer"
1931 },
1929 "total_score": { 1932 "total_score": {
1930 "type": "integer" 1933 "type": "integer"
1931 }, 1934 },
1932 "user": { 1935 "user": {
1933 "$ref": "#/definitions/models.UserShort" 1936 "$ref": "#/definitions/models.UserShortWithAvatar"
1934 } 1937 }
1935 } 1938 }
1936 }, 1939 },
diff --git a/docs/swagger.yaml b/docs/swagger.yaml
index 3e83fe8..6cd460f 100644
--- a/docs/swagger.yaml
+++ b/docs/swagger.yaml
@@ -351,7 +351,7 @@ definitions:
351 properties: 351 properties:
352 p2sr: 352 p2sr:
353 type: string 353 type: string
354 stream: 354 steam:
355 type: string 355 type: string
356 twitch: 356 twitch:
357 type: string 357 type: string
@@ -441,10 +441,12 @@ definitions:
441 type: object 441 type: object
442 models.UserRanking: 442 models.UserRanking:
443 properties: 443 properties:
444 placement:
445 type: integer
444 total_score: 446 total_score:
445 type: integer 447 type: integer
446 user: 448 user:
447 $ref: '#/definitions/models.UserShort' 449 $ref: '#/definitions/models.UserShortWithAvatar'
448 type: object 450 type: object
449 models.UserShort: 451 models.UserShort:
450 properties: 452 properties:
generated by cgit v1.2.3 (git 2.39.1) at 2025-11-08 23:24:30 +0000