Chapters

10 Chapters | 50:57 |

01

Doctrine DQL

4:58
02

The QueryBuilder

2:02
03

And WHERE Or WHERE

4:27
04

JOINs!

3:27
05

Joins and addSelect Reduce Queries

6:03
06

SELECT the SUM (or COUNT)

4:51
07

Selecting Specific Fields

5:04
08

Raw SQL Queries

6:29
09

Reusing Queries with the Query Builder

3:07
10

Filters

10:29

This tutorial has a new version, check it out!

> Symfony 5 > Go Pro with Doctrine Queries (Legacy)

Buy Access to Course

05.

Joins and addSelect Reduce Queries

Autoplay

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.

Start your All-Access Pass

Buy just this tutorial for $6.00

Previous Chapter

Next Chapter

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:

            Copy Code

                            Show All Lines

                        19 lines
            |
            app/Resources/views/fortune/homepage.html.twig
        
                Show Lines

                                                    // ... lines 1 - 5
                            
                {% for category in categories %}
        
                Show Lines

                                                    // ... lines 7 - 9
                            
                                <span class="fa {{ category.iconKey }}"></span> {{ category.name }}  ({{ category.fortuneCookies|length }})
        
                Show Lines

                                                    // ... lines 11 - 15
                            
                {% endfor %}
        
                Show Lines

                                                    // ... 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'):

            Copy Code

                            Show All Lines

                        37 lines
            |
            src/AppBundle/Entity/CategoryRepository.php
        
                Show Lines

                                                    // ... lines 1 - 23
                            
                public function search($term)
        
                {
        
                    return $this->createQueryBuilder('cat')
        
                Show Lines

                                                    // ... lines 27 - 29
                            
                        ->leftJoin('cat.fortuneCookies', 'fc')
        
                        ->addSelect('fc')
        
                Show Lines

                                                    // ... lines 32 - 34
                            
                }
        
                Show Lines

                                                    // ... 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'):

39 lines | src/AppBundle/Entity/CategoryRepository.php

// ... lines 1 - 39

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:

            Copy Code

                            Show All Lines

                        53 lines
            |
            src/AppBundle/Controller/FortuneController.php
        
                Show Lines

                                                    // ... lines 1 - 35
                            
                public function showCategoryAction($id)
        
                {
        
                Show Lines

                                                    // ... lines 38 - 41
                            
                    $category = $categoryRepository->find($id);
        
                Show Lines

                                                    // ... lines 43 - 50
                            
                }
        
                Show Lines

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

            Copy Code

                            Show All Lines

                        50 lines
            |
            src/AppBundle/Entity/CategoryRepository.php
        
                Show Lines

                                                    // ... lines 1 - 12
                            
            class CategoryRepository extends EntityRepository
        
            {
        
                Show Lines

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

11 Comments

Sort By

Rsteuber 5 years ago

Hey,

Im using the querybuilder for joining tables. Now i want to use criteria but the matching method is not available on array that is returned from the querybuilder. How can i use criteria in my querybuilder result?

| Reply |

Victor SFCASTS Rsteuber 5 years ago

Hey Rob,

As I understand you want to apply Doctrine criteria on the simple PHP array - you can't. Criteria can be applied on Doctrine's ArrayCollection only. Or instead of returning the result array in your repository you can return the query builder, i.e. do not call "->getQuery()->getResult()" and then you can call "->addCriteria()" on query builder. Or, even better, just pass the criteria to the repository method and apply them with "->addCriteria()" on query builder, then return the simple PHP array back.

Cheers!

| Reply |

Rsteuber Rsteuber 5 years ago

Never mind. passed it trough an arrayCollection. :)

| Reply |

Victor SFCASTS Rsteuber 5 years ago

Hey Rob,

Ah, noticed your comment too late. Glad you solved it yourself, but I left a few ideas for you in my previous comment :)

I hope this helps!

Cheers!

| Reply |

Jeffrey C. 6 years ago

Hey,
Just an idea when i follow the turorial with works but it might be an good idea to update it to 5.4 instead to keep it at 5.3 because short array syntax only then got introduced in PHP.

But still keep up the good work guys love the tutorials.

Cheers,

| Reply |

ahmedbhs 6 years ago

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()?

| Reply |

weaverryan SFCASTS ahmedbhs 6 years ago

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!

| Reply |

asfsdfas weaverryan 3 years ago

From one of the Symfony tutorials I recall you can also do fetch=EXTRA_LAZY, would that make sense here?

| Reply |

Victor SFCASTS asfsdfas 3 years ago

Hey Asfsdfas,

That "fetch=EXTRA_LAZY" allows you to not load the full collection on some operations like count, etc - it will call a separate query avoiding loading the full collection into memory unless you will start iterate over it. It's a good performance trick sometimes. You can read more about it in Doctrine docs here: https://www.doctrine-projec...

Cheers!

| Reply |

Caim Astraea 6 years ago

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!="">>

| Reply |

Victor SFCASTS Caim Astraea 6 years ago

Hey Caim o/

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

Cheers!

| Reply |

This course is built on Symfony 2, but most of the concepts apply just fine to newer versions of Symfony. If you have questions, let us know :).

Symfony 2

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": ">=5.3.3, <7.3.0",
        "symfony/symfony": "2.6.*", // v2.6.13
        "doctrine/orm": "~2.2,>=2.2.3", // v2.4.8
        "doctrine/doctrine-bundle": "~1.2", // 1.6.4
        "twig/extensions": "~1.0", // v1.5.4
        "symfony/assetic-bundle": "~2.3", // v2.8.2
        "symfony/swiftmailer-bundle": "~2.3", // v2.3.12
        "symfony/monolog-bundle": "~2.4", // v2.12.1
        "sensio/distribution-bundle": "~3.0.12", // v3.0.36
        "sensio/framework-extra-bundle": "~3.0", // v3.0.29
        "incenteev/composer-parameter-handler": "~2.0", // v2.1.3
        "hautelook/alice-bundle": "0.2.*" // 0.2
    },
    "require-dev": {
        "sensio/generator-bundle": "~2.3" // v2.5.3
    }
}

Previous Chapter

Next Chapter

Joins and addSelect Reduce Queries

Share this awesome video!

Keep on Learning!

Reducing Queries with addSelect

addSelect and the Return Value

Adding addSelect to findAllOrdered()

Adding addSelect to find()

11 Comments

Delete comment?

What PHP libraries does this tutorial use?

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

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

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

Show Lines	// ... lines 1 - 12
	class CategoryRepository extends EntityRepository
	{
Show Lines	// ... 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();
	}
	}