> Symfony 6 > Go Pro with Doctrine Queries
Buy Access to Course
06.

EXTRA_LAZY Relationships

|

Share this awesome video!

|

Keep on Learning!

Start your All-Access Pass

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

Click back to the homepage with no search query. We still have seven queries because we're still using our very simple findAllOrdered() method... which doesn't have the JOIN. So... we should add the JOIN here too, right? Yep! Well... probably. But I want to show you an alternative solution.

Our homepage is unique because we don't really need all the FortuneCookie data for each Category... the only thing we need is the COUNT.

Check out the template: we're not looping over category.fortuneCookies and rendering the actual FortuneCookie data. Nope, we're simply counting them. If you think about it, having a giant query that grabs all of the FortuneCookie data.... just to count them... isn't the greatest thing for efficiency.

17 lines | templates/fortune/homepage.html.twig
// ... 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

Adding fetch: EXTRA_LAZY

If you find yourself in this situation, you can tell Doctrine to be clever with how it loads the relation. Go into the Category entity and find the OneToMany relationship for $fortuneCookies. At the end, add fetch: set to EXTRA_LAZY.

91 lines | src/Entity/Category.php
// ... lines 1 - 10
class Category
{
// ... lines 13 - 23
#[ORM\OneToMany(mappedBy: 'category', targetEntity: FortuneCookie::class, fetch: 'EXTRA_LAZY')]
private Collection $fortuneCookies;
// ... lines 26 - 89
}

Let's go see what that does. When you refresh, watch the query count. It stays at seven! But if we open up the profiler, the queries themselves have changed. The first one is the same: it queries from category. But check out the others! We have SELECT COUNT(*) FROM fortune_cookie over and over! So we do have seven queries, but now each is only selecting the COUNT!

When you have fetch: 'EXTRA_LAZY' and you simply count a collection relation, Doctrine is smart enough to select just the COUNT instead of querying for all the data. If we were to loop over this collection and start printing out FortuneCookie data, then it would still make a full query for the data. But if all we need is to count them, then fetch: 'EXTRA_LAZY' is a great solution.

Custom Query on the Category Show Page

Ok: click into one of the categories. The profiler says that we have two queries. This is a, sort of, "miniature" N+1 problem. The first query selects a single Category... and the second selects all the fortune cookies for this one category. Let's flex our JOIN skills to get this down to one query.

Open up FortuneController and find the showCategory() action. By type-hinting Category on this argument, we're telling Symfony to query for the Category for us, by using the {id}. Normally, I love this! However, in this case, because we want to add a JOIN from Category to fortuneCookies, we need to take control of that query.

37 lines | src/Controller/FortuneController.php
// ... lines 1 - 11
class FortuneController extends AbstractController
{
// ... lines 14 - 29
public function showCategory(Category $category): Response
{
return $this->render('fortune/showCategory.html.twig',[
'category' => $category
]);
}
}

Change this so that Symfony passes us the int $id directly. Then, autowire CategoryRepository $categoryRepository.

39 lines | src/Controller/FortuneController.php
// ... lines 1 - 11
class FortuneController extends AbstractController
{
// ... lines 14 - 29
public function showCategory(int $id, CategoryRepository $categoryRepository): Response
{
// ... lines 32 - 36
}
}

Below, do the query manually with $category = $categoryRepository->... calling a new method: findWithFortunesJoin($id). Before we create that, we also need to add if (!$category), then throw $this->createNotFoundException(). You can give that a message if you want.

Ok, copy the method name, hop over to CategoryRepository and say public function findWithFortunesJoin(int $id), which will return a Category if one is found, else null. I'll fix that typo in a minute.

42 lines | src/Controller/FortuneController.php
// ... lines 1 - 29
public function showCategory(int $id, CategoryRepository $categoryRepository): Response
{
$category = $categoryRepository->findWithFortunesJoin($id);
if (!$category) {
throw $this->createNotFoundException('Category not found!');
}
// ... lines 36 - 39
}
// ... lines 41 - 42

The query starts like the other.... and we could steal some code... but since we're practicing, let's write it by hand. return $this->createQueryBuilder() and pass our normal category alias. Then ->andWhere('category.id = :id') - I'll fix that typo in a minutes as well - filling in the wildcard with ->setParameter() id, $id... ideally spelled correctly. Then ->getQuery().

107 lines | src/Repository/CategoryRepository.php
// ... lines 1 - 17
class CategoryRepository extends ServiceEntityRepository
{
// ... lines 20 - 52
public function findWithFortunesJoin(int $id): ?Category
{
return $this->createQueryBuilder('category')
// ... lines 56 - 57
->andWhere('category.id = :id')
->setParameter('id', $id)
->getQuery()
// ... line 61
}
// ... lines 63 - 105
}

Until now, we've been fetching multiple rows... and so we've used ->getResult(). But this time, we want either the one result or null if it can't be found. To do that, use ->getOneOrNullResult().

107 lines | src/Repository/CategoryRepository.php
// ... lines 1 - 52
public function findWithFortunesJoin(int $id): ?Category
{
return $this->createQueryBuilder('category')
// ... lines 56 - 60
->getOneOrNullResult();
}
// ... lines 63 - 107

And that's it! That should get things working. I'll do a little sanity check over here, and... oh... it would probably help if I typed things correctly. But this is cool! It recognized that it didn't know what that alias was and gave us a clear error. And now... it works, and we still have two queries.

Adding a Join

Time for the JOIN! We're going from one Category to many fortune cookies, so let's say ->leftJoin() on category. and the property name, which is fortuneCookies. Once again, the order doesn't matter, but above I'll say ->addSelect('fortuneCookie'). Oh, and I also need to add fortuneCookie as a second argument inside the ->leftJoin(): that's the alias.

107 lines | src/Repository/CategoryRepository.php
// ... lines 1 - 52
public function findWithFortunesJoin(int $id): ?Category
{
return $this->createQueryBuilder('category')
->addSelect('fortuneCookie')
->leftJoin('category.fortuneCookies', 'fortuneCookie')
// ... lines 58 - 61
}
// ... lines 63 - 107

So we're aliasing that joined entity to fortuneCookie then selecting fortuneCookie. Now, we should see this query number go from two to one. And... it did!

Here's the takeaway: while there's no need to over-optimize, if you have the N+1 problem, you can solve it by JOINing to the related table and selecting its data.

Ok, until now, Doctrine has returned a collection of Category objects or a single Category object. That's cool, but what if, instead of entire objects, we just need some data - like a few columns, a COUNT, or a SUM? Let's dig into that next.