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

Symfony 5 >

COURSE

Doctrine, Symfony & the Database

Buy Access to Course

Reusing Query Logic & Param Converters

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

Maybe my favorite thing about the QueryBuilder is that if you have multiple methods inside a repository, you can reuse query logic between them. For example, a lot of queries might need this andWhere('q.askedAt IS NOT NULL') logic. That's not complex, but I would still love to not repeat this line over and over again in every method and query. Instead, let's centralize this logic.

Private Method to Mutate a QueryBuilder

Create a new private function at the bottom. Let's call it addIsAskedQueryBuilder() with a QueryBuilder argument - the one from ORM. Make this also return a QueryBuilder.

54 lines src/Repository/QuestionRepository.php
... lines 1 - 6
use Doctrine\ORM\QueryBuilder;
... lines 8 - 15
class QuestionRepository extends ServiceEntityRepository
{
... lines 18 - 36
private function addIsAskedQueryBuilder(QueryBuilder $qb): QueryBuilder
{
... line 39
}
... lines 41 - 52
}

Inside, we're going to modify the QueryBuilder that's passed to us to add the custom logic. So, $qb-> and then copy the andWhere('q.askedAt IS NOT NULL'). Oh, and return this.

54 lines src/Repository/QuestionRepository.php
... lines 1 - 15
class QuestionRepository extends ServiceEntityRepository
{
... lines 18 - 36
private function addIsAskedQueryBuilder(QueryBuilder $qb): QueryBuilder
{
return $qb->andWhere('q.askedAt IS NOT NULL');
}
... lines 41 - 52
}

Pretty much every QueryBuilder method returns itself, which is nice because it allows us to do method chaining. By returning the QueryBuilder from our method, we will also be able to chain off of it.

Ok, back in the original method, first create a QueryBuilder and set it to a variable. So, $qb = $this->createQueryBuilder().

54 lines src/Repository/QuestionRepository.php
... lines 1 - 15
class QuestionRepository extends ServiceEntityRepository
{
... lines 18 - 25
public function findAllAskedOrderedByNewest()
{
$qb = $this->createQueryBuilder('q');
... lines 29 - 34
}
... lines 36 - 52
}

Then we can say return $this->addIsAskedQueryBuilder($qb) and then the rest of the query.

54 lines src/Repository/QuestionRepository.php
... lines 1 - 15
class QuestionRepository extends ServiceEntityRepository
{
... lines 18 - 25
public function findAllAskedOrderedByNewest()
{
$qb = $this->createQueryBuilder('q');
return $this->addIsAskedQueryBuilder($qb)
->orderBy('q.askedAt', 'DESC')
->getQuery()
->getResult()
;
}
... lines 36 - 52
}

How cool is that? We now have a private method that we can call whenever we have a query that should only return published questions. And as a bonus... when we refresh... it doesn't break!

Making the QueryBuilder Argument Option

But it is kind of a bummer that we needed to first create this empty QueryBuilder. It broke our cool-looking method chaining. Let's see if we can improve this.

Create another private method at the bottom called getOrCreateQueryBuilder(). This will accept an optional QueryBuilder argument - so QueryBuilder $qb = null. And, it will return a QueryBuilder.

58 lines src/Repository/QuestionRepository.php
... lines 1 - 15
class QuestionRepository extends ServiceEntityRepository
{
... lines 18 - 40
private function getOrCreateQueryBuilder(QueryBuilder $qb = null): QueryBuilder
{
... line 43
}
... lines 45 - 56
}

This is totally a convenience method. If the QueryBuilder is passed, return it, else, return $this->createQueryBuilder() using the same q alias.

58 lines src/Repository/QuestionRepository.php
... lines 1 - 15
class QuestionRepository extends ServiceEntityRepository
{
... lines 18 - 40
private function getOrCreateQueryBuilder(QueryBuilder $qb = null): QueryBuilder
{
return $qb ?: $this->createQueryBuilder('q');
}
... lines 45 - 56
}

This is useful because, in addIsAskedQueryBuilder(), we can add = null to make its QueryBuilder argument optional. Make this work by saying return $this->getOrCreateQueryBuilder() passing $qb. Then ->andWhere('q.askedAt IS NOT NULL')

58 lines src/Repository/QuestionRepository.php
... lines 1 - 15
class QuestionRepository extends ServiceEntityRepository
{
... lines 18 - 34
private function addIsAskedQueryBuilder(QueryBuilder $qb = null): QueryBuilder
{
return $this->getOrCreateQueryBuilder($qb)
->andWhere('q.askedAt IS NOT NULL');
}
... lines 40 - 56
}

So, if somebody passes us an existing QueryBuilder, we use it! But if not, we'll create an empty QueryBuilder automatically. That's customer service!

All of this basically just makes the helper method easier to use above. Now we can just return $this->addIsAskedQueryBuilder() with no $qb argument.

58 lines src/Repository/QuestionRepository.php
... lines 1 - 15
class QuestionRepository extends ServiceEntityRepository
{
... lines 18 - 25
public function findAllAskedOrderedByNewest()
{
return $this->addIsAskedQueryBuilder()
->orderBy('q.askedAt', 'DESC')
->getQuery()
->getResult()
;
}
... lines 34 - 56
}

Before we celebrate and throw a well-deserved taco party, let's make sure it works. Refresh and... it does! Sweet! Tacos!

Next, I've got another shortcut to show you! This time it's about letting Symfony query for an object automatically in the controller... a feature I love.

Leave a comment!

Most Useful Newest Oldest
8
Login or Register to join the conversation
Trendency Avatar
Trendency Avatar Trendency | posted 1 year ago | edited

`
public function findAllAskedOrderedByNewest()

{

    return $this->addIsAskedQueryBuilder()
        ->orderBy('q.askedAt', 'DESC')
        ->getQuery()
        ->getResult()
    ;
}

private function addIsAskedQueryBuilder(): QueryBuilder
{
    return $this->getOrCreateQueryBuilder()
    ->andWhere('q.askedAt IS NOT NULL');
}

private function getOrCreateQueryBuilder(QueryBuilder  $qb  = null ):QueryBuilder{
    return $qb ?: $this->createQueryBuilder('q');
}

`

QueryBuilder $qb does not need to be specified for addIsAskedQueryBuilder because getOrCreateQueryBuilder is already specified

right ?

Reply
Victor Avatar

Hey Sandor,

Yes, you're correct! The QueryBuilder instance is already created in getOrCreateQueryBuilder() and because we return it there - we can just use it upstream in addIsAskedQueryBuilder() and return it again so we could continue with the chain in findAllAskedOrderedByNewest() as well :)

Cheers!

Reply
zahariastefan462 Avatar

Once a query was made, $qb will return to be null?
In witch conditions $qb will be null?
Thank you in advance

Reply
MolloKhan Avatar

Hey zahariastefan462

I believe you got confused by our custom private method getOrCreateQueryBuilder(). It accepts an optional QueryBuilder but it will alwasy return one.

Cheers!

1 Reply
zahariastefan462 Avatar

Thank you for reply. I think I understood now.

Reply
Farah Avatar

I don't understand why you don't delete the ->andWhere('q.askedAt IS NOT NULL') line. Isn't that the purpose of the addIsAskedQueryBuilder to be able to avoid duplicate code?

Reply
weaverryan Avatar

Hi @Farah!

OMG, that's 100% my fault! I was so focused on the refactoring that I forget to actually take *advantage* of the refactoring. Yes, I intended to remove, because it's now redundant. I'll add a note to the video so that others don't get confused.

Thanks for the message on this!

Reply
Farah Avatar

Thank you very much for your reply! I just got confused for a second. I totally get being focused on a task and forgetting everything else haha! Keep up the amazing tutorials they‘re seriously the best out there and actually help me so much at me new job!

Reply
Cat in space

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

This tutorial also works great for Symfony 6!
Symfony 5 Doctrine 2

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.4.1",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "doctrine/doctrine-bundle": "^2.1", // 2.1.1
        "doctrine/doctrine-migrations-bundle": "^3.0", // 3.0.2
        "doctrine/orm": "^2.7", // 2.8.2
        "knplabs/knp-markdown-bundle": "^1.8", // 1.9.0
        "knplabs/knp-time-bundle": "^1.11", // v1.16.0
        "sensio/framework-extra-bundle": "^6.0", // v6.2.1
        "sentry/sentry-symfony": "^4.0", // 4.0.3
        "stof/doctrine-extensions-bundle": "^1.4", // v1.5.0
        "symfony/asset": "5.1.*", // v5.1.2
        "symfony/console": "5.1.*", // v5.1.2
        "symfony/dotenv": "5.1.*", // v5.1.2
        "symfony/flex": "^1.3.1", // v1.17.5
        "symfony/framework-bundle": "5.1.*", // v5.1.2
        "symfony/monolog-bundle": "^3.0", // v3.5.0
        "symfony/stopwatch": "5.1.*", // v5.1.2
        "symfony/twig-bundle": "5.1.*", // v5.1.2
        "symfony/webpack-encore-bundle": "^1.7", // v1.8.0
        "symfony/yaml": "5.1.*", // v5.1.2
        "twig/extra-bundle": "^2.12|^3.0", // v3.0.4
        "twig/twig": "^2.12|^3.0" // v3.0.4
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.3", // 3.4.0
        "symfony/debug-bundle": "5.1.*", // v5.1.2
        "symfony/maker-bundle": "^1.15", // v1.23.0
        "symfony/var-dumper": "5.1.*", // v5.1.2
        "symfony/web-profiler-bundle": "5.1.*", // v5.1.2
        "zenstruck/foundry": "^1.1" // v1.5.0
    }
}
Edit on GitHub
Previous Chapter
Next Chapter
userVoice