Friday night, and the tavern is packed. Bruna has added the Friday feast to the menu, twenty-four dishes, and the ingredients now show on the menu too. The result: the /menu page crawls, and the database server is smoking harder than the Fire pepper.
Pippin has installed a counter: at the bottom right of every page, the number of SQL queries it cost. Open the preview.
The N+1 problem
The template loops over the categories, then over the dishes of each category, then over the ingredients of each dish. Doctrine loads relations lazily: it only queries the database when a collection is touched. That gives:
- 1 query for the categories;
- N queries, one per category, for its dishes;
- one more per dish, for its ingredients.
Each line looks harmless, but the number of queries grows with the menu.
Your mission
/menumust load the whole menu (categories, dishes, ingredients) in a single query;- in the same order as before: categories by position, dishes from cheapest to most expensive, ingredients in alphabetical order;
- without losing anything: a category that is still empty, or a dish without ingredients, stays on the menu;
- same for the menu for a budget (
/menu?budget=5).
The template does not change: it is the query that must bring, in one go, everything the template is going to read.