Too Many Queries in Go

Every query is a trip to the database. A route that runs a query inside a loop makes one trip per element: one query to list the orders, then one more for each order. With 7 orders that is 1 + 7 = 8 queries; with 10,000 orders, 10,001. This is the N+1 problem: the answer is right, but the route gets slower as the shop grows.

It hides in any loop that runs a query. Here it is on another route, a list of customers with their orders:

rows, _ := db.Query("SELECT id, name FROM customers")
defer rows.Close()
for rows.Next() {
	var c Customer
	rows.Scan(&c.ID, &c.Name)
	orders, _ := db.Query("SELECT * FROM orders WHERE customer_id = ?", c.ID)
	// ... one more query for every customer
	orders.Close()
}

The fix is one query with a JOIN. It sends back one row per item, with the columns of its order next to it, and ORDER BY keeps the rows of each order together. Your Go code then groups the rows into orders, with no more queries. In this lesson the check counts the queries of each request and shows the count at /check/queries:

GET /orders         →  200  [{"id": 1, "status": "paid", "items": [{"product_id": 1, "quantity": 2}, {"product_id": 4, "quantity": 5}]}, {"id": 2, "status": "paid", "items": [{"product_id": 3, "quantity": 1}]}, … 7 orders]
GET /check/queries  →  200  {"queries": 1}
POST /orders  {"customer_id": 5, "items": [{"product_id": 8, "quantity": 1}, {"product_id": 5, "quantity": 2}]}  →  201  {"id": 8}
GET /orders         →  200  [… the same 7 orders, then {"id": 8, "status": "new", "items": [{"product_id": 8, "quantity": 1}, {"product_id": 5, "quantity": 2}]}]
GET /check/queries  →  200  {"queries": 1}

Your task: at the end of newApp, GET /orders is started for you: the first lines of its query, rows, result (a slice of the given OrderWithItems) and the answer. Finish it so it answers every order with its id, its status and its items, each with its product_id and quantity, and asks the database only once.

Assignment
Follow the Coding Tutorial to build GET /orders with one JOIN query, and group its rows into orders in Go.

Hint
A row starts a new order when result is still empty, or when the last order in it, result[len(result)-1], has another ID. Check len(result) == 0 first: indexing an empty slice panics. Every row, new order or not, appends its line to the last order's Items.