Pages of Results

A real shop can have thousands of products, far too many for one answer. So clients ask for one page at a time: /products?page=2&per_page=3 means pages of 3 products, and the second one. The answer also gives the total, how many products match in all, and next_page, the page to ask for next, or null when there is none.

SQL cuts out one page with LIMIT and OFFSET: OFFSET skips rows, then LIMIT gives at most that many of the rows after them. To count the rows a query gives, wrap it in SELECT COUNT(*) FROM (...). On the orders table, with 5 orders per page:

-- page 3 of the new orders: skip 10, give the next 5
SELECT * FROM orders WHERE status = 'new' LIMIT 5 OFFSET 10
-- how many new orders there are in all
SELECT COUNT(*) FROM (SELECT * FROM orders WHERE status = 'new')

In C#, a route parameter like int? limit is null when the URL has no limit. The ?? operator gives it a default: limit = limit ?? 5; makes it 5 then. To read a single value, like a count, run the command with cmd.ExecuteScalar().

Your GET /products must give these answers:

GET /products?page=2&per_page=3
    →  200  {"products": [Pen, Plant, Teapot], "total": 8, "next_page": 3}
GET /products?page=3&per_page=3
    →  200  {"products": [Stapler, Gift card], "total": 8, "next_page": null}
GET /products?page=4&per_page=3
    →  200  {"products": [], "total": 8, "next_page": null}
GET /products?category=office&per_page=2
    →  200  {"products": [Notebook, Pen], "total": 3, "next_page": 2}
GET /products?category=office&per_page=2&page=2
    →  200  {"products": [Stapler], "total": 3, "next_page": null}
GET /products?max_price=10&per_page=3
    →  200  {"products": [Notebook, Pen, Stapler], "total": 3, "next_page": null}
GET /products
    →  200  {"products": [all 8 products], "total": 8, "next_page": null}
GET /products?page=0&per_page=3
    →  400  {"error": "page and per_page must be 1 or more"}

Each product in "products" is the full object, like {"id": 4, "name": "Pen", "price": 2, "category": "office", "stock": 120}.

Your task: make GET /products answer one page at a time, with the filters still working. Set page to 1 and per_page to 10 when the URL leaves them out, and answer an object with the page's products, the total and the next_page. A page or per_page below 1 gets a 400.

Assignment
Follow the Coding Tutorial to count the matching products, give the answer its new shape, and cut one page out with LIMIT and OFFSET.

Hint
Page page starts after (page - 1) * per_page rows. Count the total before you add LIMIT to the query, or it will count only one page. $limit and $offset get their values with cmd.Parameters.AddWithValue, like the filters.