Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine
TRACK

Symfony 6 >

COURSE

Go Pro with Doctrine Queries

Buy Access to Course

Reusing Queries in the Query Builder

English Español

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
Edit on GitHub

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

Login Subscribe

Open up CategoryRepository. We have a few places in here where we ->leftJoin() over to fortuneCookies and select fortune cookies. In the future, we may need to do that in even more methods... so it would be super-duper if we could reuse that logic instead of repeating it over and over again. Let's do that!

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')
->addSelect('fortuneCookie')
->leftJoin('category.fortuneCookies', 'fortuneCookie')
... lines 58 - 61
}
... lines 63 - 105
}

Anywhere inside here, add a new private function called addFortuneCookieJoinAndSelect(). This will accept a QueryBuilder object (make sure you get the one from Doctrine\ORM - the "Object Relational Mapper"), and let's call it $qb. This will also return a QueryBuilder.

113 lines src/Repository/CategoryRepository.php
... lines 1 - 7
use Doctrine\ORM\QueryBuilder;
... lines 9 - 18
class CategoryRepository extends ServiceEntityRepository
{
... lines 21 - 82
private function addFortuneCookieJoinAndSelect(QueryBuilder $qb): QueryBuilder
{
}
... lines 87 - 111
}

The next step is pretty simple. Go steal the JOIN logic from above... and, down here, say return $qb... and paste that... being sure to clean up any spacing mess that may have occurred.

115 lines src/Repository/CategoryRepository.php
... lines 1 - 82
private function addFortuneCookieJoinAndSelect(QueryBuilder $qb): QueryBuilder
{
return $qb
->addSelect('fortuneCookie')
->leftJoin('category.fortuneCookies', 'fortuneCookie');
}
... lines 89 - 115

And... done! We can now call this method, pass in the QueryBuilder, and it will add the JOIN and SELECT for us.

The result is pretty nice. Up here, we can say $qb = $this->createQueryBuilder('category')... then below, return $this->addFortuneCookieJoinAndSelect() passing $qb.

115 lines src/Repository/CategoryRepository.php
... lines 1 - 41
public function search(string $term): array
{
$qb = $this->createQueryBuilder('category');
return $this->addFortuneCookieJoinAndSelect($qb)
... lines 47 - 51
}
... lines 53 - 115

We create the $qb, pass it to the method, it modifies it... then also returns the QueryBuilder, so we can just chain off of it like normal.

Spin over and try the "Search" feature. And... oh... of course that breaks! We need to remove this excess code. If we try it now... great success!

To celebrate, repeat that same thing down here. Replace return with $qb =... below that, say return $this->addFortuneCookieJoinAndSelect() passing in $qb, and then remove ->addSelect() and ->leftJoin().

115 lines src/Repository/CategoryRepository.php
... lines 1 - 53
public function findWithFortunesJoin(int $id): ?Category
{
$qb = $this->createQueryBuilder('category');
return $this->addFortuneCookieJoinAndSelect($qb)
->andWhere('category.id = :id')
->setParameter('id', $id)
->getQuery()
->getOneOrNullResult();
}
... lines 64 - 115

This is for the Category page, so if we click any category... perfect! It's still rocking.

Making the QueryBuilder Argument Optional

But... we can even make this even nicer! Instead of requiring the QueryBuilder object as an argument, make it optional.

Watch: down here, tweak this so that if we have a $qb, use it, otherwise, $this->createQueryBuilder('category'). So if a QueryBuilder was passed in, use this and call ->addSelect(), else create a fresh QueryBuilder and call ->addSelect() on that.

111 lines src/Repository/CategoryRepository.php
... lines 1 - 78
private function addFortuneCookieJoinAndSelect(QueryBuilder $qb = null): QueryBuilder
{
return ($qb ?? $this->createQueryBuilder('category'))
... lines 82 - 83
}
... lines 85 - 111

The advantage is that we don't need to initialize our QueryBuilder at all up here... and the same thing goes for the method above.

111 lines src/Repository/CategoryRepository.php
... lines 1 - 41
public function search(string $term): array
{
return $this->addFortuneCookieJoinAndSelect()
... lines 45 - 49
}
... line 51
public function findWithFortunesJoin(int $id): ?Category
{
return $this->addFortuneCookieJoinAndSelect()
... lines 55 - 58
}
... lines 60 - 111

But you can see how important it is that we're using a consistent alias everywhere. We're referencing category.name,category.iconKey, and category.id... so we need to make sure that we always create a QueryBuilder using that exact alias. Else... things would get explodey.

Let's add one more reusable method: private function addOrderByCategoryName()... because we're probably going to want to always order our data in the same way. Give this the usual QueryBuilder $qb = null argument, return a QueryBuilder, and the inside is pretty simple. I'll steal the code above... let me hit "enter" so it looks a bit better... and then start the same way. Create a QueryBuilder if we need to, and then say ->addOrderBy('category.name'), followed by Criteria::DESC, which we used earlier in our search() method. And yes, we are sorting in reverse alphabetical order because, well, honestly I have no idea what I was thinking when I coded that part.

117 lines src/Repository/CategoryRepository.php
... lines 1 - 85
private function addOrderByCategoryName(QueryBuilder $qb = null): QueryBuilder
{
return ($qb ?? $this->createQueryBuilder('category'))
->addOrderBy('category.name', Criteria::DESC);
}
... lines 91 - 117

To use this, we need to break things up a bit. Start with $qb = $this->addOrderByCategoryName() and pass nothing. Then pass that $qb to the second part.

118 lines src/Repository/CategoryRepository.php
... lines 1 - 41
public function search(string $term): array
{
$qb = $this->addOrderByCategoryName();
return $this->addFortuneCookieJoinAndSelect($qb)
... lines 47 - 50
}
... lines 52 - 118

As soon as you have multiple shortcut methods, you can't chain them all... which is a small bummer. But this does still allow us to remove the ->addOrderBy() down here.

If we try it now... the page still works! And if we try searching for something on the homepage... that's looking good too!

Next: let's learn about the Criteria system: a really cool way to efficiently filter collection relationships inside the database, while keeping your code dead-simple.

Leave a comment!

Most Useful Newest Oldest
4
Login or Register to join the conversation
Claudio-B Avatar
Claudio-B Avatar Claudio-B | posted 1 month ago | edited

Before aksing, thank you so much for your great job.

I get this error:

App\Model\CategoryFortuneStats::__construct(): Argument #1 ($fortunesPrinted) must be of type int, null given, called in C:\wamp64\www\mixed_vinyl\src\Repository\FortuneCookieRepository.php on line .."

if a category is empty (0 fortuneCoockie). How to check the case in CategoryFortuneStats constructor?

Thank you in advance
Claudio

Reply
Victor Avatar

Hey @Claudio-B ,

Well, you just need make sure you pass an integer to the constructor of your CategoryFortuneStats, i.e. instead of null pass 0 for example. To avoid multiple checks in multiple places before new CategoryFortuneStats() you can make sure your repository method that calculates the number always returns an integer, i.e. do something like this:

if ($result === null) {
    return 0;
}

But if you want to do this check in the constructor of CategoryFortuneStats - just allow passing null for that fortunesPrinted argument, i.e. 

public function __construct(?int $fortunesPrinted)
{
    if ($fortunesPrinted === null) {
        $fortunesPrinted = 0;
    }
    $this->fortunesPrinted = $fortunesPrinted;
}

Something like this, I hope this helps! :)

Cheers!

Reply
Claudio-B Avatar

Very kind of you!
Ciao!

Reply
Victor Avatar

Glad it helped! :)

Cheers!

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

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
Previous Chapter
Next Chapter
userVoice