From 40537dace937fc96c851c56f0cb629f36dddfd03 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Tue, 20 Jun 2023 19:55:31 +0300
Subject: feat: improved map summary response, all routes init (#43, #46)

---
 backend/controllers/mapController.go | 52 +++++++++++++-----------------------
 1 file changed, 18 insertions(+), 34 deletions(-)

(limited to 'backend/controllers/mapController.go')

diff --git a/backend/controllers/mapController.go b/backend/controllers/mapController.go
index 37b8e9b..e46b766 100644
--- a/backend/controllers/mapController.go
+++ b/backend/controllers/mapController.go
@@ -20,60 +20,44 @@ import (
 //	@Router		/maps/{id}/summary [get]
 func FetchMapSummary(c *gin.Context) {
 	id := c.Param("id")
-	// Get map data
-	response := models.MapSummaryResponse{Map: models.Map{}, Summary: models.MapSummary{History: []models.MapHistory{}, Routes: []models.MapRoute{}}}
+	response := models.MapSummaryResponse{Map: models.Map{}, Summary: models.MapSummary{Routes: []models.MapRoute{}}}
 	intID, err := strconv.Atoi(id)
 	if err != nil {
 		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 		return
 	}
+	// (
+	// 	SELECT COALESCE(avg(rating), 0.0)
+	// 	FROM route_ratings
+	// 	WHERE map_id = $1
+	// )
+	// Get map data
 	response.Map.ID = intID
-	sql := `SELECT m.id, g.name, c.name, m.name,
-	(
-		SELECT COALESCE(avg(rating), 0.0)
-		FROM map_ratings
-		WHERE map_id = $1
-	)
+	sql := `SELECT m.id, g.name, c.name, m.name
 	FROM maps m
 	INNER JOIN games g ON m.game_id = g.id
 	INNER JOIN chapters c ON m.chapter_id = c.id
 	WHERE m.id = $1`
-	err = database.DB.QueryRow(sql, id).Scan(&response.Map.ID, &response.Map.GameName, &response.Map.ChapterName, &response.Map.MapName, &response.Summary.Rating)
+	err = database.DB.QueryRow(sql, id).Scan(&response.Map.ID, &response.Map.GameName, &response.Map.ChapterName, &response.Map.MapName)
 	if err != nil {
 		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 		return
 	}
-	sql = `SELECT user_name, score_count, record_date
-	FROM map_history
-	WHERE map_id = $1
-	ORDER BY record_date ASC`
+	// Get map routes and histories
+	sql = `SELECT c.id, c.name, h.user_name, h.score_count, h.record_date, r.score_count, 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 c.id, h.user_name, h.score_count, h.record_date, r.score_count, r.description, r.showcase
+	ORDER BY h.record_date ASC;`
 	rows, err := database.DB.Query(sql, id)
 	if err != nil {
 		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 		return
 	}
 	for rows.Next() {
-		history := models.MapHistory{}
-		err = rows.Scan(&history.RunnerName, &history.ScoreCount, &history.Date)
-		if err != nil {
-			c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
-			return
-		}
-		response.Summary.History = append(response.Summary.History, history)
-	}
-	sql = `SELECT c.id, c.name, mr.score_count, mr.description, mr.showcase
-	FROM map_routes mr
-	INNER JOIN categories c ON mr.category_id = c.id
-	WHERE mr.map_id = $1
-	ORDER BY mr.score_count DESC`
-	rows, err = database.DB.Query(sql, id)
-	if err != nil {
-		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
-		return
-	}
-	for rows.Next() {
-		route := models.MapRoute{}
-		err = rows.Scan(&route.Category.ID, &route.Category.Name, &route.ScoreCount, &route.Description, &route.Showcase)
+		route := models.MapRoute{Category: models.Category{}, History: models.MapHistory{}}
+		err = rows.Scan(&route.Category.ID, &route.Category.Name, &route.History.RunnerName, &route.History.ScoreCount, &route.History.Date, &route.ScoreCount, &route.Description, &route.Showcase, &route.Rating)
 		if err != nil {
 			c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 			return
-- 
cgit v1.2.3