fix(backend): fix an issue where listing query does not specify order by clause

2 files changed, 15 insertions, 5 deletions

diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go
index 89506d0..18acfda 100644
--- a/backend/db/query.sql.go
+++ b/backend/db/query.sql.go
@@ -162,6 +162,7 @@ const listGamePlayers = `-- name: ListGamePlayers :many
 SELECT game_id, game_players.user_id, users.user_id, username, display_name, icon_path, is_admin, created_at FROM game_players
 LEFT JOIN users ON game_players.user_id = users.user_id
 WHERE game_players.game_id = $1
+ORDER BY game_players.user_id
 `
 
 type ListGamePlayersRow struct {
@@ -207,6 +208,7 @@ func (q *Queries) ListGamePlayers(ctx context.Context, gameID int32) ([]ListGame
 const listGames = `-- name: ListGames :many
 SELECT game_id, game_type, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description FROM games
 LEFT JOIN problems ON games.problem_id = problems.problem_id
+ORDER BY games.game_id
 `
 
 type ListGamesRow struct {
@@ -260,6 +262,7 @@ SELECT games.game_id, game_type, state, display_name, duration_seconds, created_
 LEFT JOIN problems ON games.problem_id = problems.problem_id
 JOIN game_players ON games.game_id = game_players.game_id
 WHERE game_players.user_id = $1
+ORDER BY games.game_id
 `
 
 type ListGamesForPlayerRow struct {
@@ -315,6 +318,7 @@ func (q *Queries) ListGamesForPlayer(ctx context.Context, userID int32) ([]ListG
 const listTestcasesByGameID = `-- name: ListTestcasesByGameID :many
 SELECT testcase_id, problem_id, stdin, stdout FROM testcases
 WHERE testcases.problem_id = (SELECT problem_id FROM games WHERE game_id = $1)
+ORDER BY testcases.testcase_id
 `
 
 func (q *Queries) ListTestcasesByGameID(ctx context.Context, gameID int32) ([]Testcase, error) {
@@ -344,6 +348,7 @@ func (q *Queries) ListTestcasesByGameID(ctx context.Context, gameID int32) ([]Te
 
 const listUsers = `-- name: ListUsers :many
 SELECT user_id, username, display_name, icon_path, is_admin, created_at FROM users
+ORDER BY users.user_id
 `
 
 func (q *Queries) ListUsers(ctx context.Context) ([]User, error) {
diff --git a/backend/query.sql b/backend/query.sql
index 6395b9b..ea04c08 100644
--- a/backend/query.sql
+++ b/backend/query.sql
@@ -4,7 +4,8 @@ WHERE users.user_id = $1
 LIMIT 1;
 
 -- name: ListUsers :many
-SELECT * FROM users;
+SELECT * FROM users
+ORDER BY users.user_id;
 
 -- name: GetUserAuthByUsername :one
 SELECT * FROM users
@@ -14,13 +15,15 @@ LIMIT 1;
 
 -- name: ListGames :many
 SELECT * FROM games
-LEFT JOIN problems ON games.problem_id = problems.problem_id;
+LEFT JOIN problems ON games.problem_id = problems.problem_id
+ORDER BY games.game_id;
 
 -- name: ListGamesForPlayer :many
 SELECT * FROM games
 LEFT JOIN problems ON games.problem_id = problems.problem_id
 JOIN game_players ON games.game_id = game_players.game_id
-WHERE game_players.user_id = $1;
+WHERE game_players.user_id = $1
+ORDER BY games.game_id;
 
 -- name: UpdateGameState :exec
 UPDATE games
@@ -41,7 +44,8 @@ LIMIT 1;
 -- name: ListGamePlayers :many
 SELECT * FROM game_players
 LEFT JOIN users ON game_players.user_id = users.user_id
-WHERE game_players.game_id = $1;
+WHERE game_players.game_id = $1
+ORDER BY game_players.user_id;
 
 -- name: UpdateGame :exec
 UPDATE games
@@ -61,7 +65,8 @@ RETURNING submission_id;
 
 -- name: ListTestcasesByGameID :many
 SELECT * FROM testcases
-WHERE testcases.problem_id = (SELECT problem_id FROM games WHERE game_id = $1);
+WHERE testcases.problem_id = (SELECT problem_id FROM games WHERE game_id = $1)
+ORDER BY testcases.testcase_id;
 
 -- name: CreateTestcaseExecution :exec
 INSERT INTO testcase_executions (submission_id, testcase_id, status, stdout, stderr)