Project: The Category Menu

The storefront now has a category menu: one button per category, with the number of products in it, like office (3). On open, it asks your API for GET /api/categories, a route the server doesn't have yet.

The database can do the counting. GROUP BY puts the rows with the same value into one group, and COUNT(*) then counts the rows of each group. AS renames a column, so the JSON gets the keys you choose. On a table of orders, for example:

SELECT status, COUNT(*) AS orders FROM orders GROUP BY status ORDER BY status

answers one row per status, like {"status": "paid", "orders": 5}. The clauses always come in this order: SELECT … FROM, then WHERE (which rows to keep), then GROUP BY, then ORDER BY. Without ORDER BY, the database may answer in any order.

A column with no value holds NULL. NULL rows make a group of their own, and only IS NOT NULL filters them out: != NULL never matches anything. The Gift card has no category, so it must not become a button. Your API and the menu must give:

GET /api/categories  →  200  [{"count": 2, "name": "home"}, {"count": 2, "name": "kitchen"}, {"count": 3, "name": "office"}]

open the page        →  buttons: All (pressed), home (2), kitchen (2), office (3)
click "office (3)"   →  GET /api/products?q=&category=office&page=1&per_page=3  →  Showing 3 of 3

Your task: in server/app.js, above the /api 404 handler, write a function listCategories(req, res) that builds the query in sql, runs it and answers the rows as JSON, and register it for GET /api/categories.

Assignment
Follow the Coding Tutorial to write listCategories, build its query step by step, and register it for GET /api/categories.

Hint
Each piece you add to sql with += starts with a space, and the pieces must end up in SQL's order: FROM, WHERE, GROUP BY, ORDER BY. Give app.get the function's name, without parentheses.