Too Many Queries

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:

customers = db.execute("SELECT * FROM customers").fetchall()
for customer in customers:
    orders = db.execute("SELECT * FROM orders WHERE customer_id = ?", (customer["id"],)).fetchall()

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 Python 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 the file, add GET /orders. It answers every order with its id, its status and its items, each with its product_id and quantity, and it 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 Python.

Hint
A row starts a new order when result is still empty, or when the last order in it, result[-1], has another id. Check for the empty list first: result[-1] on an empty list is an error. Every row, new order or not, adds its item to the last order.