JOINs and addSelect Reduce Queries

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

When 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.

            
            37 lines
            
            src/Controller/FortuneController.php
        
                                                    ... 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.

            
            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

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 FortuneCookie data 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').

            
            96 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... 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.

Leave a comment!

RM | posted 1 month ago

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)

/**
 * @param int $root_node_id
 * @return array
 */
public function getCategoryTreeNodeArrayByRootId(int $root_node_id): array
{
    $query = $this->createQueryBuilder('c')
        ->from(Category::class, 'c')
        ->where('c.root = :root')
        ->setParameter('root', $root_node_id)
        ->orderBy('c.root, c.lft', 'ASC')
        ->getQuery();

    $query->setHint(Query::HINT_INCLUDE_META_COLUMNS, true);

    return $query->getArrayResult();
}

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.

sadikoff | SFCASTS | RM | posted 1 month ago | edited

Hey @RM

You don't need to ->addSelect() with root alias as it's already added. So the question is which real queries do you want to minimize? Because this query should return the whole tree in scalar array view, and if you need additional data from relations you should add joins here and add this relation to ->addSelect()

Cheers.

RM | sadikoff | posted 1 month ago

Ok, thank you!

Jerzy | posted 2 months ago

Hi Ryan,

The strange thing is that the counts of fortune cookies for categories have changed after adding the join. On video it's 2:40 vs 3:44. But it feels like these shouldn't change because it was a left join that was added. Perhaps some data was added/removed during video recording?

PS. thanks for another great course!

sadikoff | SFCASTS | Jerzy | posted 2 months ago

Hey, hey I'm here with updates. Yeah that was a correct situation, because you are on search page and you are limiting results to "be" query, so adding select with join fixed issue with counting, now it shows the correct number of elements in that category

Because you have this fortuneCookie.fortune LIKE :searchTerm in query

Cheers!

1 Reply

Jerzy | sadikoff | posted 2 months ago

Hi Vladimir,
Thanks for checking!

So if I understand correctly:
At first the |length in Twig made it count all the records within a category, because Doctrine detected that the relation isn't hydrated and it fetched all the records from the db. What was tricky here for me is that this query was already filtering related fortuneCookies by the search query.

But adding an addSelect('fortuneCookie') made Doctrine hydrate the relation only with the records matching the LIKE condition, and then |length already had related(filtered out) rows. So Doctrine didn't need to resort back to the default relation mapping(no filtering) to fetch the related fortuneCookies.

I guess that makes sense now!

sadikoff | SFCASTS | Jerzy | posted 2 months ago

Yeah that's correct! Sometimes it can be tricky!

Cheers and happy coding!

1 Reply

sadikoff | SFCASTS | Jerzy | posted 2 months ago

That is a great catch, I'll check the details and get back to you =)

Symfony 6

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": ">=8.1",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "beberlei/doctrineextensions": "^1.3", // v1.3.0
        "doctrine/doctrine-bundle": "^2.7", // 2.9.1
        "doctrine/doctrine-migrations-bundle": "^3.2", // 3.2.2
        "doctrine/orm": "^2.13", // 2.15.1
        "symfony/asset": "6.2.*", // v6.2.7
        "symfony/console": "6.2.*", // v6.2.10
        "symfony/dotenv": "6.2.*", // v6.2.8
        "symfony/flex": "^2", // v2.2.5
        "symfony/framework-bundle": "6.2.*", // v6.2.10
        "symfony/proxy-manager-bridge": "6.2.*", // v6.2.7
        "symfony/runtime": "6.2.*", // v6.2.8
        "symfony/twig-bundle": "6.2.*", // v6.2.7
        "symfony/webpack-encore-bundle": "^1.16", // v1.16.1
        "symfony/yaml": "6.2.*" // v6.2.10
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.4", // 3.4.4
        "symfony/maker-bundle": "^1.47", // v1.48.0
        "symfony/stopwatch": "6.2.*", // v6.2.7
        "symfony/web-profiler-bundle": "6.2.*", // v6.2.10
        "zenstruck/foundry": "^1.22" // v1.32.0
    }
}

Edit on GitHub

TRACK

Symfony 6 >

COURSE

Go Pro with Doctrine Queries

JOINs and addSelect Reduce Queries

Keep on Learning!

Lazy-Loading Relationships

The N+1 Problem

Selecting the Joined Fields

Where does the Join Data Hide?

Leave a comment!

What PHP libraries does this tutorial use?

	... 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
	}

	... 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

	... 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
	}