Buy
Buy

Joins and addSelect Reduce Queries

There's a problem with our page! Sorry, I'll stop panicking - it's not a huge deal, but if you look at the bottom, two queries are being executed. That's strange: the only query I remember making is inside my FortuneController when we call search().

Click the web debug toolbar to see what the queries are. Ah, the first I recognize: that's our search query. But the second one is something different. Look closely: it's querying for all the fortune_cookie rows that are related to this category:

SELECT t0.* FROM fortune_cookie t0
    WHERE t0.category_id = 4;

If you've heard about "lazy loading" of relationships, you probably know what this comes from. The query is actually coming from our template. We loop over the array of Category object, and then print category.fortuneCookies|length:

... lines 1 - 5
{% for category in categories %}
... lines 7 - 9
<span class="fa {{ category.iconKey }}"></span> {{ category.name }} ({{ category.fortuneCookies|length }})
... lines 11 - 15
{% endfor %}
... lines 17 - 19

The Category object has all of the data for itself, but at this point, it hasn't yet fetched the data of the FortuneCookie's that it's related to in the database. So at the moment we call category.fortuneCookies and try to count the results, it goes out and does a query for all of the fortune_cookie rows for this category. That's called a lazy query, because it was lazy and waited to be executed until we actually needed that data.

This "extra query" isn't the end of the world. In fact, I don't usually fix it until I'm working on my page's performance. On the homepage without a search, it's even more noticeable. We have 7 queries here: one for the categories, and one extra query to get the fortune cookies for each category in the list. That makes 2, 3, 4, 5, 6 and 7 queries. This is a classic problem called the n+1 problem.

And again, it's not the end of the world - so don't over-optimize. But let's fix it here.

Reducing Queries with addSelect

Back in CategoryRepository, once we've joined over to our fortuneCookies, we can say ->addSelect('fc'):

... lines 1 - 23
public function search($term)
{
return $this->createQueryBuilder('cat')
... lines 27 - 29
->leftJoin('cat.fortuneCookies', 'fc')
->addSelect('fc')
... lines 32 - 34
}
... lines 36 - 37

And just by doing that, our second query is gone! It's black magic - don't worry about how it works! You know I'm kidding, here's the deal. Remember that when we call $this->createQueryBuilder() from inside a repository class, that automatically selects everything from the Category. So it's equivalent to calling ->select('cat'). Calling addSelect() means that we're going to select all the Category information and all the FortuneCookie information.

addSelect and the Return Value

There's one super-important thing to keep in mind: even though we're selecting more data, this function returns the exact same thing it did before: an array of Category objects. That's different from SQL, where selecting all the fields from a joined table will give you more fields in your result. Here, addSelect() just tells Doctrine to fetch the FortuneCookie data, but store it internally. Later, when we access the FortuneCookies for a Category, it's smart enough to know that it doesn't need that second query. So we can reduce the number of queries used without needing to go update any other code: this function still returns an array of Categories.

Adding addSelect to findAllOrdered()

Go back to the homepage without a search. Dang, we still have those 7 ugly queries. And that's because this uses a different method: findAllOrdered. Let's to the same thing here. ->leftJoin('cat.fortuneCookies', 'fc') and then an addSelect('fc'):

Our two queries start to have some duplication. That's an issue we'll fix later. We're hoping to see our 7 queries drop all the way to 1 - the one query for all of the Categories. Perfect!

Adding addSelect to find()

We're on a roll! Click into a category - like Proverbs. Here, we have two queries. This is the same problem - query #1 is the one we're doing in our controller. Query #2 comes lazily from the template, where we're looping over all the fortune cookies for the category.

We're using the built-in find() method in the controller:

... lines 1 - 35
public function showCategoryAction($id)
{
... lines 38 - 41
$category = $categoryRepository->find($id);
... lines 43 - 50
}
... lines 52 - 53

But since it doesn't let us do any joins, we need to do something more custom. Call a new method findWithFortunesJoin. You know the drill: we'll go into CategoryRepository and then add that method. And at this point, this should be a really easy query. I'll copy the search() function, then simplify things in the andWhere: cat.id = :id. We want to keep the leftJoin() and the addSelect to remove the extra query. Update setParameter to set the id placeholder:

... lines 1 - 12
class CategoryRepository extends EntityRepository
{
... lines 15 - 38
public function findWithFortunesJoin($id)
{
return $this->createQueryBuilder('cat')
->andWhere('cat.id = :id')
->leftJoin('cat.fortuneCookies', 'fc')
->addSelect('fc')
->setParameter('id', $id)
->getQuery()
->getOneOrNullResult();
}
}

The execute() function returns an array of results, but in this case, we want just one Category object, or null if there isn't one. So we'll use the other function I talked about to finish the query: getOneOrNullResult().

Refresh! Two queries is now 1.

Exactly like in SQL, JOINs have two purposes. Sometimes you JOIN because you want to add a WHERE clause or an ORDER BY on the data on that JOIN'ed table. The second reason to JOIN is that you actually want to SELECT data from the table. In Doctrine, this second reason feels a little different because even though we're SELECTing from the fortune_cookie table, the query still returns the same array of Category objects as before. But Doctrine has that extra data in the background.

But this doesn't have to be the case. Over the next two chapters, we'll start SELECT'ing individual fields, instead of entire objects.

Leave a comment!

  • 2018-11-09 weaverryan

    Hey @Batiste!

    Very good question! Let's see if we can shed some life on these :).

    1) first, the ->innerJoin() and ->join() methods on the QueryBuilder object are identical - check it out here: https://github.com/doctrine...

    Second, in general, the difference between an innerJoin and a leftJoin deals with whether you are joining over to *one* matching row or many matching rows. For example, in this video, each Category has many FortuneCookies. So, we want to "left join" because we're joining over to "many" rows. This is probably an imprecise explanation, but it's a simple way that helps me think about it. Now, look at the other direction: suppose we were querying for 10 FortuneCookie objects, but (maybe to save a query) we want to join over to Category. In this case, each row in FortuneCookie will join to *one* Category. So, I would use an innerJoin.

    2) fetch="EAGER" is ALMOST the same as using the addSelect(), but not quite. With the addSelect() strategy, we are making ONE query that contains a JOIN to get all of our data. With fetch="EAGER", there are 2 queries: one for the original Category and then a second query for all of the FortuneCookies related to that Category (this works even if you fetch multiple Category objects - I'm pretty sure the second query would query for ALL of the FortunateCookies you need for all of the Categories). And of course, the addSelect() way only applies to a single query, where fetch="EAGER" makes that behavior always happen, which can be nice for convenience.

    Let me know if this makes sense! Great questions!

    Cheers!

  • 2018-11-08 Batiste

    Hey, I think doctrine present a lot of joins types (innerJoin, Join, leftJoin, outerJoin...bla bla) :( What I'm supposed to learn with symfony as Join types.
    1) Waht is the dffierence between InnerJoin, Join and leftJoin ?
    2) Does addading the fetch='EAGER' annotation on $frtuneCookies do the same job of addSelect()?

  • 2016-08-03 Victor Bocharsky

    Hey Caim o/

    Thanks for reporting this, we have some problem with code blocks inaccessibility. I've updated the cache, now it works.

    Cheers!

  • 2016-08-03 Caim Astraea

    Hello. o/
    ( Aside )Seems there's some problem with the formatting? I'm seeing some blocks <<an asp="" developer="" stole="" our="" code="" block.="" quick="" -="" chase="" them!="">>