JOINs and addSelect Reduce Queries
Keep on Learning!
If you liked what you've learned so far, dive in! Subscribe to get access to this tutorial plus video, code and script downloads.
With a Subscription, click any sentence in the script to jump to that part of the video!
Login SubscribeWhen we're on the homepage, we see seven queries. We have one to get all the categories... then additional queries to get all the fortune cookies for each category. We can see this in the profiler. This is the main query FROM category... then each of these down here is selecting fortune cookie data for a specific category: 3, 4, 2, 6, and so on.
Lazy-Loading Relationships
If you've used Doctrine, you probably recognize what's happening. Doctrine loads its relationships lazily. Let's follow the logic. In FortuneController, we start by querying for an array of $categories.
| // ... lines 1 - 11 | |
| class FortuneController extends AbstractController | |
| { | |
| // ... line 14 | |
| public function index(Request $request, CategoryRepository $categoryRepository): Response | |
| { | |
| $searchTerm = $request->query->get('q'); | |
| if ($searchTerm) { | |
| $categories = $categoryRepository->search($searchTerm); | |
| } else { | |
| $categories = $categoryRepository->findAllOrdered(); | |
| } | |
| return $this->render('fortune/homepage.html.twig',[ | |
| 'categories' => $categories | |
| ]); | |
| } | |
| // ... lines 28 - 35 | |
| } |
In that query, if we look at it, it's only selecting category data: not fortune cookie data. But if we go into the template - templates/fortune/homepage.html.twig - we loop over the categories and eventually call category.fortuneCookies|length.
| // ... lines 1 - 7 | |
| {% for category in categories %} | |
| <a class="bg-orange-400 hover:bg-orange-500 text-white text-center rounded-full p-4" href="{{ path('app_category_show', {'id': category.id}) }}"> | |
| <span class="fa {{ category.iconKey }}"></span> <span class="font-bold text-lg">{{ category.name }}</span> ({{ category.fortuneCookies|length }}) | |
| </a> | |
| // ... lines 12 - 13 | |
| {% endfor %} | |
| // ... lines 15 - 17 |
The N+1 Problem
In PHP land, we're calling the getFortuneCookies() method on Category. But until now, Doctrine has not yet queried for the FortuneCookie data for this Category. However, as soon as we access the $this->fortuneCookies property, it magically makes that query, basically saying:
Give me all the
FortuneCookiedata for this category
Which... it then sets onto the property and returns back to us. So it's at this moment inside of Twig when that second, third, fourth, fifth, sixth, and seventh query is executed.
This is called the "N+1 Problem", where you have "N" number of queries for the related items on your page "plus one" for the main query. In our case, it's 1 main query for the categories plus 6 more queries to get the fortune cookie data for those 6 categories.
This isn't necessarily a problem. It might hurt performance on your page... or be no big deal. But if it is slowing things down, we can fix it with a JOIN. After all, when we query for the categories, we're already joining over to the fortune cookie table. So... if we just grab the fortine cookie data in the first query, couldn't we build this whole page with that one query? The answer is... totally!
Selecting the Joined Fields
To see this in action, search for something first. I'm doing this because it will trigger the search() method in our repository, which already has the JOIN. Over here, since we have five results, it made six queries.
Okay, we're already joining over to fortuneCookie. So how can we select its data? It's delightfully simple. And again, order doesn't matter: ->addSelect('fortuneCookie').
| // ... lines 1 - 17 | |
| class CategoryRepository extends ServiceEntityRepository | |
| { | |
| // ... lines 20 - 40 | |
| public function search(string $term): array | |
| { | |
| return $this->createQueryBuilder('category') | |
| ->addSelect('fortuneCookie') | |
| ->leftJoin('category.fortuneCookies', 'fortuneCookie') | |
| // ... lines 46 - 50 | |
| } | |
| // ... lines 52 - 94 | |
| } |
That's it! Try this thing! The queries went down to one and the page still works!
Tip
You might notice that the fortune cookie count for each category also change. Before,
Doctrine executed separate queries to count the related fortune cookies without
considering our search term. But after adding addSelect('fortuneCookie'), the ORM
uses that data to count instead of making new queries... which includes our search
term!
If you open the profiler... and view the formatted query... yes! It's joining over to fortune_cookie and grabbing the fortune_cookie data at the same time. The "N+1" problem is solved!
Where does the Join Data Hide?
But I want to point out one key thing. Because we're inside of CategoryRepository, when we call $this->createQueryBuilder('category'), that automatically adds a ->select('category') to the query. We know that.
However now we're selecting all of the category and fortuneCookie data. But... our page still works... which must mean that even though we're selecting data from two tables, our query is still returning the same thing it did before an array of Category objects. It's not returning some mixture of category and fortuneCookie data.
This point can be a bit confusing, so let me break it down. When we call createQueryBuilder(), that actually adds 2 things to our query: FROM App\Entity\Category as category and SELECT category. Thanks to the FROM, Category is our "root entity" and, unless we start doing something more complex, Doctrine will try to return Category objects. When we ->addSelect('fortuneCookie'), instead of returning a mixture of categories and fortune cookies, Doctrine basically grabs the fortuneCookie data and stores it for later. Then, if we ever call $category->getFortuneCookies(), it realizes that it already has that data, so instead of making a query, it uses it.
The really important thing is that when we use ->addSelect() to grab the data from a JOIN, it does not change what our method returns. Though later, we will see times when using select() or addSelect() does change what our query returns.
Ok, so we just used a JOIN to reduce our queries from 7 to 1. However, because we're only counting the number of fortune cookies for each category, there is another solution. Let's talk about EXTRA_LAZY relationships next.
Hello, Ryan!
[1] Really handy optimization for reducing query count with addSelect().
[2] Is there an equivalent method of reducing query count for a Tree?
(Tree - Nestedset behavior extension for Doctrine)
when i add
->addSelect('c')i get an error
[Semantical Error] line 0, col 73 near 'c WHERE c.root': Error: 'c' is already defined.