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:

const customers = db.prepare("SELECT * FROM customers").all();
for (const customer of customers) {
  const orders = db.prepare("SELECT * FROM orders WHERE customer_id = ?").all(customer.id);
}

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 TypeScript code then groups the rows into orders, with no more queries. The types for it are ready at the top of the file: OrderItemRow for one row of the JOIN, OrderWithItems for an order in the answer. 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 TypeScript.

Hint
A row starts a new order when result is still empty, or when the last order in it, result[result.length - 1], has another id. Check for the empty array first: the last element of an empty array is undefined, and reading .id from it is an error when the code runs, even though TypeScript doesn't warn you. Every row, new order or not, adds its item to the last order.