aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--backend/database/functions.sql212
-rw-r--r--backend/handlers/home.go56
-rw-r--r--backend/handlers/user.go493
3 files changed, 414 insertions, 347 deletions
diff --git a/backend/database/functions.sql b/backend/database/functions.sql
new file mode 100644
index 0000000..ca33a60
--- /dev/null
+++ b/backend/database/functions.sql
@@ -0,0 +1,212 @@
1CREATE OR REPLACE FUNCTION get_rankings_singleplayer()
2RETURNS TABLE (
3 steam_id TEXT,
4 user_name TEXT,
5 avatar_link TEXT,
6 total_min_score_count BIGINT
7) AS $$
8BEGIN
9 RETURN QUERY
10 SELECT
11 u.steam_id,
12 u.user_name,
13 u.avatar_link,
14 (
15 SELECT SUM(min_score_count) AS total_min_score_count
16 FROM (
17 SELECT sp.user_id, MIN(sp.score_count) AS min_score_count
18 FROM records_sp sp
19 WHERE sp.is_deleted = false
20 GROUP BY sp.user_id, sp.map_id
21 ) AS subquery
22 WHERE user_id = u.steam_id
23 )
24 FROM records_sp sp
25 JOIN users u ON u.steam_id = sp.user_id
26 WHERE sp.is_deleted = false
27 GROUP BY u.steam_id, u.user_name, u.avatar_link
28 HAVING COUNT(DISTINCT sp.map_id) = (
29 SELECT COUNT(m.name)
30 FROM maps m
31 INNER JOIN games g ON m.game_id = g.id
32 WHERE g.id = 1 AND m.is_disabled = false
33 )
34 ORDER BY total_min_score_count ASC;
35END;
36$$ LANGUAGE plpgsql;
37
38
39
40CREATE OR REPLACE FUNCTION get_rankings_multiplayer()
41RETURNS TABLE (
42 steam_id TEXT,
43 user_name TEXT,
44 avatar_link TEXT,
45 total_min_score_count BIGINT
46) AS $$
47BEGIN
48 RETURN QUERY
49 SELECT
50 u.steam_id,
51 u.user_name,
52 u.avatar_link,
53 (
54 SELECT SUM(min_score_count) AS total_min_score_count
55 FROM (
56 SELECT DISTINCT ON (map_id, player_id)
57 map_id,
58 player_id,
59 MIN(score_count) AS min_score_count
60 FROM (
61 SELECT
62 mp.map_id,
63 mp.host_id AS player_id,
64 mp.score_count
65 FROM records_mp mp
66 WHERE mp.is_deleted = false
67 UNION ALL
68 SELECT
69 mp.map_id,
70 mp.partner_id AS player_id,
71 mp.score_count
72 FROM records_mp mp
73 WHERE mp.is_deleted = false
74 ) AS player_scores
75 GROUP BY map_id, player_id
76 ) AS subquery
77 WHERE player_id = u.steam_id
78 )
79 FROM records_mp mp
80 JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id
81 WHERE mp.is_deleted = false
82 GROUP BY u.steam_id, u.user_name, u.avatar_link
83 HAVING COUNT(DISTINCT mp.map_id) = (
84 SELECT COUNT(m.name)
85 FROM maps m
86 INNER JOIN games g ON m.game_id = g.id
87 WHERE g.id = 2 AND m.is_disabled = false
88 )
89 ORDER BY total_min_score_count ASC;
90END;
91$$ LANGUAGE plpgsql;
92
93
94
95CREATE OR REPLACE FUNCTION get_placements_singleplayer(player_id TEXT)
96RETURNS TABLE (
97 map_id SMALLINT,
98 placement BIGINT
99) AS $$
100BEGIN
101 RETURN QUERY
102 WITH ranked_scores AS (
103 SELECT
104 sp.map_id,
105 sp.user_id,
106 sp.score_count,
107 sp.score_time,
108 ROW_NUMBER() OVER (
109 PARTITION BY sp.map_id, sp.user_id
110 ORDER BY sp.score_count ASC, sp.score_time ASC
111 ) AS rank
112 FROM records_sp sp
113 WHERE sp.is_deleted = false
114 ),
115 best_scores AS (
116 SELECT
117 rs.map_id,
118 rs.user_id,
119 rs.score_count,
120 rs.score_time
121 FROM ranked_scores rs
122 WHERE rs.rank = 1
123 ),
124 min_placements AS (
125 SELECT
126 bs.map_id,
127 bs.user_id,
128 (SELECT COUNT(*) + 1
129 FROM best_scores AS inner_scores
130 WHERE inner_scores.map_id = bs.map_id
131 AND (inner_scores.score_count < bs.score_count
132 OR (inner_scores.score_count = bs.score_count
133 AND inner_scores.score_time < bs.score_time)
134 )
135 ) AS placement
136 FROM best_scores AS bs
137 )
138 SELECT
139 minp.map_id,
140 MIN(minp.placement) AS placement
141 FROM min_placements minp
142 WHERE minp.user_id = get_placements_singleplayer.player_id
143 GROUP BY minp.map_id
144 ORDER BY minp.map_id, placement;
145END;
146$$ LANGUAGE plpgsql;
147
148
149
150CREATE OR REPLACE FUNCTION get_placements_multiplayer(player_id TEXT)
151RETURNS TABLE (
152 map_id SMALLINT,
153 placement BIGINT
154) AS $$
155BEGIN
156 RETURN QUERY
157 WITH ranked_scores AS (
158 SELECT
159 mp.map_id,
160 mp.host_id,
161 mp.partner_id,
162 mp.score_count,
163 mp.score_time,
164 ROW_NUMBER() OVER (
165 PARTITION BY mp.map_id, mp.host_id, mp.partner_id
166 ORDER BY mp.score_count ASC, mp.score_time ASC
167 ) AS rank
168 FROM records_mp mp
169 WHERE mp.is_deleted = false
170 ),
171 best_scores AS (
172 SELECT
173 rs.map_id,
174 rs.host_id,
175 rs.partner_id,
176 rs.score_count,
177 rs.score_time
178 FROM ranked_scores rs
179 WHERE rs.rank = 1
180 ),
181 min_placements AS (
182 SELECT
183 bs.map_id,
184 bs.host_id,
185 bs.partner_id,
186 (SELECT COUNT(*) + 1
187 FROM best_scores AS inner_scores
188 WHERE inner_scores.map_id = bs.map_id
189 AND (inner_scores.score_count < bs.score_count
190 OR (inner_scores.score_count = bs.score_count
191 AND inner_scores.score_time < bs.score_time)
192 )
193 ) AS placement
194 FROM best_scores AS bs
195 ),
196 distinct_min_placements AS (
197 SELECT unified_placements.map_id, unified_placements.player_id, MIN(unified_placements.placement) AS min_placement
198 FROM (
199 SELECT minp.map_id, minp.host_id AS player_id, minp.placement FROM min_placements minp
200 UNION ALL
201 SELECT minp.map_id, minp.partner_id AS player_id, minp.placement FROM min_placements minp
202 ) AS unified_placements
203 WHERE unified_placements.player_id = get_placements_multiplayer.player_id
204 GROUP BY unified_placements.map_id, unified_placements.player_id
205 )
206 SELECT
207 dminp.map_id,
208 dminp.min_placement AS placement
209 FROM distinct_min_placements dminp
210 ORDER BY dminp.map_id, placement;
211END;
212$$ LANGUAGE plpgsql;
diff --git a/backend/handlers/home.go b/backend/handlers/home.go
index 57e44c1..714610a 100644
--- a/backend/handlers/home.go
+++ b/backend/handlers/home.go
@@ -6,7 +6,6 @@ import (
6 "log" 6 "log"
7 "net/http" 7 "net/http"
8 "os" 8 "os"
9 "sort"
10 "strings" 9 "strings"
11 10
12 "lphub/database" 11 "lphub/database"
@@ -64,66 +63,33 @@ func RankingsLPHUB(c *gin.Context) {
64 Overall: []models.UserRanking{}, 63 Overall: []models.UserRanking{},
65 } 64 }
66 // Singleplayer rankings 65 // Singleplayer rankings
67 sql := `SELECT u.steam_id, u.user_name, u.avatar_link, COUNT(DISTINCT map_id), 66 rows, err := database.DB.Query(`SELECT * FROM get_rankings_singleplayer();`)
68 (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),
69 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
70 SELECT
71 user_id,
72 MIN(score_count) AS min_score_count
73 FROM records_sp WHERE is_deleted = false
74 GROUP BY user_id, map_id
75 ) AS subquery
76 WHERE user_id = u.steam_id)
77 FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id WHERE is_deleted = false GROUP BY u.steam_id, u.user_name`
78 rows, err := database.DB.Query(sql)
79 if err != nil { 67 if err != nil {
80 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 68 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
81 return 69 return
82 } 70 }
83 for rows.Next() { 71 for rows.Next() {
84 ranking := models.UserRanking{} 72 ranking := models.UserRanking{}
85 var currentCount int 73 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore)
86 var totalCount int
87 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &currentCount, &totalCount, &ranking.TotalScore)
88 if err != nil { 74 if err != nil {
89 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 75 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
90 return 76 return
91 } 77 }
92 if currentCount != totalCount {
93 continue
94 }
95 response.Singleplayer = append(response.Singleplayer, ranking) 78 response.Singleplayer = append(response.Singleplayer, ranking)
96 } 79 }
97 // Multiplayer rankings 80 // Multiplayer rankings
98 sql = `SELECT u.steam_id, u.user_name, u.avatar_link, COUNT(DISTINCT map_id), 81 rows, err = database.DB.Query(`SELECT * FROM get_rankings_multiplayer();`)
99 (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),
100 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
101 SELECT
102 host_id,
103 partner_id,
104 MIN(score_count) AS min_score_count
105 FROM records_mp WHERE is_deleted = false
106 GROUP BY host_id, partner_id, map_id
107 ) AS subquery
108 WHERE host_id = u.steam_id OR partner_id = u.steam_id)
109 FROM records_mp mp JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id WHERE is_deleted = false GROUP BY u.steam_id, u.user_name`
110 rows, err = database.DB.Query(sql)
111 if err != nil { 82 if err != nil {
112 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 83 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
113 return 84 return
114 } 85 }
115 for rows.Next() { 86 for rows.Next() {
116 ranking := models.UserRanking{} 87 ranking := models.UserRanking{}
117 var currentCount int 88 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore)
118 var totalCount int
119 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &currentCount, &totalCount, &ranking.TotalScore)
120 if err != nil { 89 if err != nil {
121 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 90 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
122 return 91 return
123 } 92 }
124 if currentCount != totalCount {
125 continue
126 }
127 response.Multiplayer = append(response.Multiplayer, ranking) 93 response.Multiplayer = append(response.Multiplayer, ranking)
128 } 94 }
129 // Has both so they are qualified for overall ranking 95 // Has both so they are qualified for overall ranking
@@ -136,12 +102,11 @@ func RankingsLPHUB(c *gin.Context) {
136 TotalScore: totalScore, 102 TotalScore: totalScore,
137 } 103 }
138 response.Overall = append(response.Overall, overallRanking) 104 response.Overall = append(response.Overall, overallRanking)
105 break
139 } 106 }
140 } 107 }
141 } 108 }
142 sort.Slice(response.Singleplayer, func(i, j int) bool { 109
143 return response.Singleplayer[i].TotalScore < response.Singleplayer[j].TotalScore
144 })
145 placement := 1 110 placement := 1
146 ties := 0 111 ties := 0
147 for index := 0; index < len(response.Singleplayer); index++ { 112 for index := 0; index < len(response.Singleplayer); index++ {
@@ -154,9 +119,7 @@ func RankingsLPHUB(c *gin.Context) {
154 } 119 }
155 placement++ 120 placement++
156 } 121 }
157 sort.Slice(response.Multiplayer, func(i, j int) bool { 122
158 return response.Multiplayer[i].TotalScore < response.Multiplayer[j].TotalScore
159 })
160 placement = 1 123 placement = 1
161 ties = 0 124 ties = 0
162 for index := 0; index < len(response.Multiplayer); index++ { 125 for index := 0; index < len(response.Multiplayer); index++ {
@@ -169,9 +132,7 @@ func RankingsLPHUB(c *gin.Context) {
169 } 132 }
170 placement++ 133 placement++
171 } 134 }
172 sort.Slice(response.Overall, func(i, j int) bool { 135
173 return response.Overall[i].TotalScore < response.Overall[j].TotalScore
174 })
175 placement = 1 136 placement = 1
176 ties = 0 137 ties = 0
177 for index := 0; index < len(response.Overall); index++ { 138 for index := 0; index < len(response.Overall); index++ {
@@ -184,6 +145,7 @@ func RankingsLPHUB(c *gin.Context) {
184 } 145 }
185 placement++ 146 placement++
186 } 147 }
148
187 c.JSON(http.StatusOK, models.Response{ 149 c.JSON(http.StatusOK, models.Response{
188 Success: true, 150 Success: true,
189 Message: "Successfully retrieved rankings.", 151 Message: "Successfully retrieved rankings.",
diff --git a/backend/handlers/user.go b/backend/handlers/user.go
index dc058c8..021a47f 100644
--- a/backend/handlers/user.go
+++ b/backend/handlers/user.go
@@ -134,162 +134,118 @@ func Profile(c *gin.Context) {
134 } 134 }
135 } 135 }
136 rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount 136 rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount
137 // Get user ranking placement for singleplayer 137 // Get user rankings. We are basically doing the same thing in RankingsLPHUB endpoint lol.
138 sql = `SELECT u.steam_id, COUNT(DISTINCT map_id), 138 rankingsList := RankingsResponse{
139 (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), 139 Singleplayer: []models.UserRanking{},
140 (SELECT SUM(min_score_count) AS total_min_score_count FROM ( 140 Multiplayer: []models.UserRanking{},
141 SELECT user_id, MIN(score_count) AS min_score_count FROM records_sp WHERE is_deleted = false GROUP BY user_id, map_id) AS subquery WHERE user_id = u.steam_id) 141 Overall: []models.UserRanking{},
142 FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id WHERE is_deleted = false GROUP BY u.steam_id, u.user_name 142 }
143 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) 143 // Singleplayer rankings
144 ORDER BY total_min_score_count ASC` 144 rows, err = database.DB.Query(`SELECT * FROM get_rankings_singleplayer();`)
145 rows, err = database.DB.Query(sql)
146 if err != nil { 145 if err != nil {
147 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 146 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
148 return 147 return
149 } 148 }
150 placement := 1
151 for rows.Next() { 149 for rows.Next() {
152 var steamID string 150 ranking := models.UserRanking{}
153 var completionCount int 151 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore)
154 var totalCount int
155 var userPortalCount int
156 err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
157 if err != nil { 152 if err != nil {
158 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 153 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
159 return 154 return
160 } 155 }
161 if completionCount != totalCount { 156 rankingsList.Singleplayer = append(rankingsList.Singleplayer, ranking)
162 placement++ 157 }
163 continue 158 // Multiplayer rankings
164 } 159 rows, err = database.DB.Query(`SELECT * FROM get_rankings_multiplayer();`)
165 if steamID != user.(models.User).SteamID {
166 placement++
167 continue
168 }
169 rankings.Singleplayer.Rank = placement
170 }
171 // Get user ranking placement for multiplayer
172 sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),
173 (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),
174 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
175 SELECT host_id, partner_id, MIN(score_count) AS min_score_count FROM records_mp WHERE is_deleted = false GROUP BY host_id, partner_id, map_id) AS subquery WHERE host_id = u.steam_id OR partner_id = u.steam_id)
176 FROM records_mp mp JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id WHERE mp.is_deleted = false GROUP BY u.steam_id, u.user_name
177 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)
178 ORDER BY total_min_score_count ASC`
179 rows, err = database.DB.Query(sql)
180 if err != nil { 160 if err != nil {
181 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 161 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
182 return 162 return
183 } 163 }
184 placement = 1
185 for rows.Next() { 164 for rows.Next() {
186 var steamID string 165 ranking := models.UserRanking{}
187 var completionCount int 166 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore)
188 var totalCount int
189 var userPortalCount int
190 err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
191 if err != nil { 167 if err != nil {
192 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 168 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
193 return 169 return
194 } 170 }
195 if completionCount != totalCount { 171 rankingsList.Multiplayer = append(rankingsList.Multiplayer, ranking)
196 placement++ 172 }
197 continue 173 // Overall rankings
174 var hasOverallPlacement bool
175 for _, spRanking := range rankingsList.Singleplayer {
176 for _, mpRanking := range rankingsList.Multiplayer {
177 if spRanking.User.SteamID == mpRanking.User.SteamID {
178 if spRanking.User.SteamID == user.(models.User).SteamID {
179 hasOverallPlacement = true
180 }
181 totalScore := spRanking.TotalScore + mpRanking.TotalScore
182 overallRanking := models.UserRanking{
183 User: spRanking.User,
184 TotalScore: totalScore,
185 }
186 rankingsList.Overall = append(rankingsList.Overall, overallRanking)
187 break
188 }
198 } 189 }
199 if steamID != user.(models.User).SteamID { 190 }
200 placement++ 191
201 continue 192 placement := 1
193 ties := 0
194 for index := 0; index < len(rankingsList.Singleplayer); index++ {
195 if index != 0 && rankingsList.Singleplayer[index-1].TotalScore == rankingsList.Singleplayer[index].TotalScore {
196 ties++
197 rankingsList.Singleplayer[index].Placement = placement - ties
198 } else {
199 ties = 0
200 rankingsList.Singleplayer[index].Placement = placement
202 } 201 }
203 rankings.Cooperative.Rank = placement 202 placement++
204 } 203 }
205 // Get user ranking placement for overall if they qualify 204
206 if rankings.Singleplayer.Rank != 0 && rankings.Cooperative.Rank != 0 { 205 placement = 1
207 sql = `WITH user_sp AS ( 206 ties = 0
208 SELECT u.steam_id, 207 for index := 0; index < len(rankingsList.Multiplayer); index++ {
209 SUM(subquery.min_score_count) AS total_min_score_count 208 if index != 0 && rankingsList.Multiplayer[index-1].TotalScore == rankingsList.Multiplayer[index].TotalScore {
210 FROM users u 209 ties++
211 LEFT JOIN ( 210 rankingsList.Multiplayer[index].Placement = placement - ties
212 SELECT user_id, map_id, MIN(score_count) AS min_score_count 211 } else {
213 FROM records_sp WHERE is_deleted = false 212 ties = 0
214 GROUP BY user_id, map_id 213 rankingsList.Multiplayer[index].Placement = placement
215 ) AS subquery ON subquery.user_id = u.steam_id
216 WHERE u.steam_id IN (
217 SELECT user_id
218 FROM records_sp sp
219 JOIN maps m ON sp.map_id = m.id
220 JOIN games g ON m.game_id = g.id
221 WHERE g.is_coop = FALSE AND m.is_disabled = FALSE AND sp.is_deleted = false
222 GROUP BY user_id
223 HAVING COUNT(DISTINCT sp.map_id) = (
224 SELECT COUNT(maps.name)
225 FROM maps
226 INNER JOIN games g ON maps.game_id = g.id
227 WHERE g.is_coop = FALSE AND maps.is_disabled = FALSE
228 )
229 )
230 GROUP BY u.steam_id
231 ), user_mp AS (
232 SELECT u.steam_id,
233 SUM(subquery.min_score_count) AS total_min_score_count
234 FROM users u
235 LEFT JOIN (
236 SELECT host_id, partner_id, map_id, MIN(score_count) AS min_score_count
237 FROM records_mp WHERE is_deleted = false
238 GROUP BY host_id, partner_id, map_id
239 ) AS subquery ON subquery.host_id = u.steam_id OR subquery.partner_id = u.steam_id
240 WHERE u.steam_id IN (
241 SELECT host_id
242 FROM records_mp mp
243 JOIN maps m ON mp.map_id = m.id
244 JOIN games g ON m.game_id = g.id
245 WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false
246 GROUP BY host_id
247 HAVING COUNT(DISTINCT mp.map_id) = (
248 SELECT COUNT(maps.name)
249 FROM maps
250 INNER JOIN games g ON maps.game_id = g.id
251 WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
252 )
253 UNION
254 SELECT partner_id
255 FROM records_mp mp
256 JOIN maps m ON mp.map_id = m.id
257 JOIN games g ON m.game_id = g.id
258 WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false
259 GROUP BY partner_id
260 HAVING COUNT(DISTINCT mp.map_id) = (
261 SELECT COUNT(maps.name)
262 FROM maps
263 INNER JOIN games g ON maps.game_id = g.id
264 WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
265 )
266 )
267 GROUP BY u.steam_id
268 )
269 SELECT COALESCE(sp.steam_id, mp.steam_id) AS steam_id,
270 sp.total_min_score_count + mp.total_min_score_count AS overall_total_min_score_count
271 FROM user_sp sp
272 INNER JOIN user_mp mp ON sp.steam_id = mp.steam_id
273 ORDER BY overall_total_min_score_count ASC`
274 rows, err = database.DB.Query(sql)
275 if err != nil {
276 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
277 return
278 } 214 }
279 placement = 1 215 placement++
280 for rows.Next() { 216 }
281 var steamID string 217
282 var userPortalCount int 218 placement = 1
283 err = rows.Scan(&steamID, &userPortalCount) 219 ties = 0
284 if err != nil { 220 for index := 0; index < len(rankingsList.Overall); index++ {
285 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 221 if index != 0 && rankingsList.Overall[index-1].TotalScore == rankingsList.Overall[index].TotalScore {
286 return 222 ties++
287 } 223 rankingsList.Overall[index].Placement = placement - ties
288 if steamID != user.(models.User).SteamID { 224 } else {
289 placement++ 225 ties = 0
290 continue 226 rankingsList.Overall[index].Placement = placement
227 }
228 placement++
229 }
230 // After we did that heavy calculation and got the rankings of ALL players, let's see if our user exists and grab the placements if they do.
231 for _, singleplayer := range rankingsList.Singleplayer {
232 if singleplayer.User.SteamID == user.(models.User).SteamID {
233 rankings.Singleplayer.Rank = singleplayer.Placement
234 break
235 }
236 }
237 for _, multiplayer := range rankingsList.Multiplayer {
238 if multiplayer.User.SteamID == user.(models.User).SteamID {
239 rankings.Cooperative.Rank = multiplayer.Placement
240 break
241 }
242 }
243 if hasOverallPlacement {
244 for _, overall := range rankingsList.Overall {
245 if overall.User.SteamID == user.(models.User).SteamID {
246 rankings.Overall.Rank = overall.Placement
247 break
291 } 248 }
292 rankings.Overall.Rank = placement
293 } 249 }
294 } 250 }
295 records := []ProfileRecords{} 251 records := []ProfileRecords{}
@@ -301,13 +257,7 @@ func Profile(c *gin.Context) {
301 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 257 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
302 return 258 return
303 } 259 }
304 sql = `WITH best_scores AS (WITH RankedScores AS (SELECT sp.user_id, sp.map_id, sp.score_count, sp.score_time, ROW_NUMBER() OVER ( 260 placementsRows, err := database.DB.Query(`SELECT * FROM get_placements_singleplayer($1);`, user.(models.User).SteamID)
305 PARTITION BY sp.user_id, sp.map_id ORDER BY sp.score_count ASC, sp.score_time ASC) AS rank FROM records_sp sp WHERE sp.is_deleted = false)
306 SELECT user_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
307 SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id
308 AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count
309 AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement`
310 placementsRows, err := database.DB.Query(sql, user.(models.User).SteamID)
311 if err != nil { 261 if err != nil {
312 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 262 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
313 return 263 return
@@ -315,8 +265,9 @@ func Profile(c *gin.Context) {
315 placements := []int{} 265 placements := []int{}
316 placementIndex := 0 266 placementIndex := 0
317 for placementsRows.Next() { 267 for placementsRows.Next() {
268 var mapID int
318 var placement int 269 var placement int
319 placementsRows.Scan(&placement) 270 placementsRows.Scan(&mapID, &placement)
320 placements = append(placements, placement) 271 placements = append(placements, placement)
321 } 272 }
322 for rows.Next() { 273 for rows.Next() {
@@ -357,13 +308,7 @@ func Profile(c *gin.Context) {
357 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 308 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
358 return 309 return
359 } 310 }
360 sql = `WITH best_scores AS (WITH RankedScores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, mp.score_count, mp.score_time, ROW_NUMBER() OVER ( 311 placementsRows, err = database.DB.Query(`SELECT * FROM get_placements_multiplayer($1);`, user.(models.User).SteamID)
361 PARTITION BY mp.host_id, mp.partner_id, mp.map_id ORDER BY mp.score_count ASC, mp.score_time ASC) AS rank FROM records_mp mp WHERE mp.is_deleted = false)
362 SELECT host_id, partner_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
363 SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id
364 AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count
365 AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement`
366 placementsRows, err = database.DB.Query(sql, user.(models.User).SteamID)
367 if err != nil { 312 if err != nil {
368 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 313 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
369 return 314 return
@@ -371,8 +316,9 @@ func Profile(c *gin.Context) {
371 placements = []int{} 316 placements = []int{}
372 placementIndex = 0 317 placementIndex = 0
373 for placementsRows.Next() { 318 for placementsRows.Next() {
319 var mapID int
374 var placement int 320 var placement int
375 placementsRows.Scan(&placement) 321 placementsRows.Scan(&mapID, &placement)
376 placements = append(placements, placement) 322 placements = append(placements, placement)
377 } 323 }
378 for rows.Next() { 324 for rows.Next() {
@@ -512,162 +458,119 @@ func FetchUser(c *gin.Context) {
512 } 458 }
513 } 459 }
514 rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount 460 rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount
515 // Get user ranking placement for singleplayer 461 // Get user rankings. We are basically doing the same thing in RankingsLPHUB endpoint lol.
516 sql = `SELECT u.steam_id, COUNT(DISTINCT map_id), 462 rankingsList := RankingsResponse{
517 (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), 463 Singleplayer: []models.UserRanking{},
518 (SELECT SUM(min_score_count) AS total_min_score_count FROM ( 464 Multiplayer: []models.UserRanking{},
519 SELECT user_id, MIN(score_count) AS min_score_count FROM records_sp WHERE is_deleted = false GROUP BY user_id, map_id) AS subquery WHERE user_id = u.steam_id) 465 Overall: []models.UserRanking{},
520 FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id WHERE is_deleted = false GROUP BY u.steam_id, u.user_name 466 }
521 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) 467 // Singleplayer rankings
522 ORDER BY total_min_score_count ASC` 468 rows, err = database.DB.Query(`SELECT * FROM get_rankings_singleplayer();`)
523 rows, err = database.DB.Query(sql)
524 if err != nil { 469 if err != nil {
525 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 470 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
526 return 471 return
527 } 472 }
528 placement := 1
529 for rows.Next() { 473 for rows.Next() {
530 var steamID string 474 ranking := models.UserRanking{}
531 var completionCount int 475 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore)
532 var totalCount int
533 var userPortalCount int
534 err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
535 if err != nil { 476 if err != nil {
536 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 477 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
537 return 478 return
538 } 479 }
539 if completionCount != totalCount { 480 rankingsList.Singleplayer = append(rankingsList.Singleplayer, ranking)
540 placement++ 481 }
541 continue 482 // Multiplayer rankings
542 } 483 rows, err = database.DB.Query(`SELECT * FROM get_rankings_multiplayer();`)
543 if steamID != user.SteamID {
544 placement++
545 continue
546 }
547 rankings.Singleplayer.Rank = placement
548 }
549 // Get user ranking placement for multiplayer
550 sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),
551 (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),
552 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
553 SELECT host_id, partner_id, MIN(score_count) AS min_score_count FROM records_mp WHERE is_deleted = false GROUP BY host_id, partner_id, map_id) AS subquery WHERE host_id = u.steam_id OR partner_id = u.steam_id)
554 FROM records_mp mp JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id WHERE mp.is_deleted = false GROUP BY u.steam_id, u.user_name
555 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)
556 ORDER BY total_min_score_count ASC`
557 rows, err = database.DB.Query(sql)
558 if err != nil { 484 if err != nil {
559 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 485 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
560 return 486 return
561 } 487 }
562 placement = 1
563 for rows.Next() { 488 for rows.Next() {
564 var steamID string 489 ranking := models.UserRanking{}
565 var completionCount int 490 err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore)
566 var totalCount int
567 var userPortalCount int
568 err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
569 if err != nil { 491 if err != nil {
570 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 492 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
571 return 493 return
572 } 494 }
573 if completionCount != totalCount { 495 rankingsList.Multiplayer = append(rankingsList.Multiplayer, ranking)
574 placement++ 496 }
575 continue 497 // Overall rankings
498 var hasOverallPlacement bool
499 for _, spRanking := range rankingsList.Singleplayer {
500 for _, mpRanking := range rankingsList.Multiplayer {
501 if spRanking.User.SteamID == mpRanking.User.SteamID {
502 if spRanking.User.SteamID == user.SteamID {
503 hasOverallPlacement = true
504 }
505 totalScore := spRanking.TotalScore + mpRanking.TotalScore
506 overallRanking := models.UserRanking{
507 User: spRanking.User,
508 TotalScore: totalScore,
509 }
510 rankingsList.Overall = append(rankingsList.Overall, overallRanking)
511 break
512 }
576 } 513 }
577 if steamID != user.SteamID { 514 }
578 placement++ 515
579 continue 516 placement := 1
517 ties := 0
518 for index := 0; index < len(rankingsList.Singleplayer); index++ {
519 if index != 0 && rankingsList.Singleplayer[index-1].TotalScore == rankingsList.Singleplayer[index].TotalScore {
520 ties++
521 rankingsList.Singleplayer[index].Placement = placement - ties
522 } else {
523 ties = 0
524 rankingsList.Singleplayer[index].Placement = placement
580 } 525 }
581 rankings.Cooperative.Rank = placement 526 placement++
582 } 527 }
583 // Get user ranking placement for overall if they qualify 528
584 if rankings.Singleplayer.Rank != 0 && rankings.Cooperative.Rank != 0 { 529 placement = 1
585 sql = `WITH user_sp AS ( 530 ties = 0
586 SELECT u.steam_id, 531 for index := 0; index < len(rankingsList.Multiplayer); index++ {
587 SUM(subquery.min_score_count) AS total_min_score_count 532 if index != 0 && rankingsList.Multiplayer[index-1].TotalScore == rankingsList.Multiplayer[index].TotalScore {
588 FROM users u 533 ties++
589 LEFT JOIN ( 534 rankingsList.Multiplayer[index].Placement = placement - ties
590 SELECT user_id, map_id, MIN(score_count) AS min_score_count 535 } else {
591 FROM records_sp WHERE is_deleted = false 536 ties = 0
592 GROUP BY user_id, map_id 537 rankingsList.Multiplayer[index].Placement = placement
593 ) AS subquery ON subquery.user_id = u.steam_id
594 WHERE u.steam_id IN (
595 SELECT user_id
596 FROM records_sp sp
597 JOIN maps m ON sp.map_id = m.id
598 JOIN games g ON m.game_id = g.id
599 WHERE g.is_coop = FALSE AND m.is_disabled = FALSE AND sp.is_deleted = false
600 GROUP BY user_id
601 HAVING COUNT(DISTINCT sp.map_id) = (
602 SELECT COUNT(maps.name)
603 FROM maps
604 INNER JOIN games g ON maps.game_id = g.id
605 WHERE g.is_coop = FALSE AND maps.is_disabled = FALSE
606 )
607 )
608 GROUP BY u.steam_id
609 ), user_mp AS (
610 SELECT u.steam_id,
611 SUM(subquery.min_score_count) AS total_min_score_count
612 FROM users u
613 LEFT JOIN (
614 SELECT host_id, partner_id, map_id, MIN(score_count) AS min_score_count
615 FROM records_mp WHERE is_deleted = false
616 GROUP BY host_id, partner_id, map_id
617 ) AS subquery ON subquery.host_id = u.steam_id OR subquery.partner_id = u.steam_id
618 WHERE u.steam_id IN (
619 SELECT host_id
620 FROM records_mp mp
621 JOIN maps m ON mp.map_id = m.id
622 JOIN games g ON m.game_id = g.id
623 WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false
624 GROUP BY host_id
625 HAVING COUNT(DISTINCT mp.map_id) = (
626 SELECT COUNT(maps.name)
627 FROM maps
628 INNER JOIN games g ON maps.game_id = g.id
629 WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
630 )
631 UNION
632 SELECT partner_id
633 FROM records_mp mp
634 JOIN maps m ON mp.map_id = m.id
635 JOIN games g ON m.game_id = g.id
636 WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false
637 GROUP BY partner_id
638 HAVING COUNT(DISTINCT mp.map_id) = (
639 SELECT COUNT(maps.name)
640 FROM maps
641 INNER JOIN games g ON maps.game_id = g.id
642 WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
643 )
644 )
645 GROUP BY u.steam_id
646 )
647 SELECT COALESCE(sp.steam_id, mp.steam_id) AS steam_id,
648 sp.total_min_score_count + mp.total_min_score_count AS overall_total_min_score_count
649 FROM user_sp sp
650 INNER JOIN user_mp mp ON sp.steam_id = mp.steam_id
651 ORDER BY overall_total_min_score_count ASC`
652 rows, err = database.DB.Query(sql)
653 if err != nil {
654 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
655 return
656 } 538 }
657 placement = 1 539 placement++
658 for rows.Next() { 540 }
659 var steamID string 541
660 var userPortalCount int 542 placement = 1
661 err = rows.Scan(&steamID, &userPortalCount) 543 ties = 0
662 if err != nil { 544 for index := 0; index < len(rankingsList.Overall); index++ {
663 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 545 if index != 0 && rankingsList.Overall[index-1].TotalScore == rankingsList.Overall[index].TotalScore {
664 return 546 ties++
665 } 547 rankingsList.Overall[index].Placement = placement - ties
666 if steamID != user.SteamID { 548 } else {
667 placement++ 549 ties = 0
668 continue 550 rankingsList.Overall[index].Placement = placement
551 }
552 placement++
553 }
554 // After we did that heavy calculation and got the rankings of ALL players, let's see if our user exists
555 // and grab the placements if they do.
556 for _, singleplayer := range rankingsList.Singleplayer {
557 if singleplayer.User.SteamID == user.SteamID {
558 rankings.Singleplayer.Rank = singleplayer.Placement
559 break
560 }
561 }
562 for _, multiplayer := range rankingsList.Multiplayer {
563 if multiplayer.User.SteamID == user.SteamID {
564 rankings.Cooperative.Rank = multiplayer.Placement
565 break
566 }
567 }
568 if hasOverallPlacement {
569 for _, overall := range rankingsList.Overall {
570 if overall.User.SteamID == user.SteamID {
571 rankings.Overall.Rank = overall.Placement
572 break
669 } 573 }
670 rankings.Overall.Rank = placement
671 } 574 }
672 } 575 }
673 records := []ProfileRecords{} 576 records := []ProfileRecords{}
@@ -679,13 +582,7 @@ func FetchUser(c *gin.Context) {
679 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 582 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
680 return 583 return
681 } 584 }
682 sql = `WITH best_scores AS (WITH RankedScores AS (SELECT sp.user_id, sp.map_id, sp.score_count, sp.score_time, ROW_NUMBER() OVER ( 585 placementsRows, err := database.DB.Query(`SELECT * FROM get_placements_singleplayer($1);`, user.SteamID)
683 PARTITION BY sp.user_id, sp.map_id ORDER BY sp.score_count ASC, sp.score_time ASC) AS rank FROM records_sp sp WHERE sp.is_deleted = false)
684 SELECT user_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
685 SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id
686 AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count
687 AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement`
688 placementsRows, err := database.DB.Query(sql, user.SteamID)
689 if err != nil { 586 if err != nil {
690 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 587 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
691 return 588 return
@@ -693,8 +590,9 @@ func FetchUser(c *gin.Context) {
693 placements := []int{} 590 placements := []int{}
694 placementIndex := 0 591 placementIndex := 0
695 for placementsRows.Next() { 592 for placementsRows.Next() {
593 var mapID int
696 var placement int 594 var placement int
697 placementsRows.Scan(&placement) 595 placementsRows.Scan(&mapID, &placement)
698 placements = append(placements, placement) 596 placements = append(placements, placement)
699 } 597 }
700 for rows.Next() { 598 for rows.Next() {
@@ -735,13 +633,7 @@ func FetchUser(c *gin.Context) {
735 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 633 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
736 return 634 return
737 } 635 }
738 sql = `WITH best_scores AS (WITH RankedScores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, mp.score_count, mp.score_time, ROW_NUMBER() OVER ( 636 placementsRows, err = database.DB.Query(`SELECT * FROM get_placements_multiplayer($1);`, user.SteamID)
739 PARTITION BY mp.host_id, mp.partner_id, mp.map_id ORDER BY mp.score_count ASC, mp.score_time ASC) AS rank FROM records_mp mp WHERE mp.is_deleted = false)
740 SELECT host_id, partner_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
741 SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id
742 AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count
743 AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement`
744 placementsRows, err = database.DB.Query(sql, user.SteamID)
745 if err != nil { 637 if err != nil {
746 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 638 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
747 return 639 return
@@ -749,8 +641,9 @@ func FetchUser(c *gin.Context) {
749 placements = []int{} 641 placements = []int{}
750 placementIndex = 0 642 placementIndex = 0
751 for placementsRows.Next() { 643 for placementsRows.Next() {
644 var mapID int
752 var placement int 645 var placement int
753 placementsRows.Scan(&placement) 646 placementsRows.Scan(&mapID, &placement)
754 placements = append(placements, placement) 647 placements = append(placements, placement)
755 } 648 }
756 for rows.Next() { 649 for rows.Next() {
generated by cgit v1.2.3 (git 2.39.1) at 2025-11-08 19:17:30 +0000