Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine

Reusing Queries with the Query Builder

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

Enough with all this SQL stuff. Remember the query builder I was raving about earlier? I promised that one of its benefits is that, with a query builder, you can re-use parts of a query. But we don't have any of that right now.

Open up CategoryRepository. We have three methods, and all of them repeat the same leftJoin() to cat.fortuneCookies and the addSelect():

... lines 1 - 14
public function findAllOrdered()
{
$qb = $this->createQueryBuilder('cat')
... line 18
->leftJoin('cat.fortuneCookies', 'fc')
->addSelect('fc');
... lines 21 - 23
}
... line 25
public function search($term)
{
return $this->createQueryBuilder('cat')
... lines 29 - 31
->leftJoin('cat.fortuneCookies', 'fc')
->addSelect('fc')
... lines 34 - 36
}
... line 38
public function findWithFortunesJoin($id)
{
return $this->createQueryBuilder('cat')
... line 42
->leftJoin('cat.fortuneCookies', 'fc')
->addSelect('fc')
... lines 45 - 47
}
... lines 49 - 50

Ah, duplication! When you see duplication like this - whether it's a WHERE, an ORDER BY or a JOIN - there's a simple solution. Just add a new private function and have it add this stuff to the query builder.

Query-modifying Functions

Create a private function called addFortuneCookieJoinAndSelect(), because that's what it's going to do! It'll accept a QueryBuilder as an argument. Our goal is to, well, add the join to that. So I'll copy the 2 pieces that we want, add a $qb, then paste it there. And just for convenience, let's return this too:

... lines 1 - 52
/**
* Joins over to cat.fortuneCookies AND selects its fields
*
* @param QueryBuilder $qb
* @return QueryBuilder
*/
private function addFortuneCookieJoinAndSelect(QueryBuilder $qb)
{
return $qb->leftJoin('cat.fortuneCookies', 'fc')
->addSelect('fc');
}
... lines 64 - 66

So, our function takes in a QueryBuilder, it modifies it, then it returns it so we can make any more changes. I'll be a nice programmer and add some PHPDoc.

Calling those Functions

The findAllOrdered() function is the one that fuels the homepage. So let's start here! Get rid of that duplicated leftJoin and addSelect. Instead, just call $this->addFortuneCookieJoinAndSelect() and pass it the $qb. So we create the query builder, do some things with it, but let our new function take care of the join stuff.

... lines 1 - 15
public function findAllOrdered()
{
$qb = $this->createQueryBuilder('cat')
->addOrderBy('cat.name', 'ASC');
$this->addFortuneCookieJoinAndSelect($qb);
$query = $qb->getQuery();
return $query->execute();
}
... lines 26 - 66

This should give us the exact same results. But you should never believe me, so let's go back and refresh the homepage. Yep, nice!

Now we get to celebrate by removing the rest of the duplication. So, addSelect and leftJoin should be gone. Instead of returning the result directly, we need to get a QueryBuilder first. So put $qb = in front and move the getQuery() stuff down and put the return in front of it. In the middle, call addFortuneCookieJoinAndSelect() like before:

... lines 1 - 26
public function search($term)
{
$qb = $this->createQueryBuilder('cat')
->andWhere('cat.name LIKE :searchTerm
OR cat.iconKey LIKE :searchTerm
OR fc.fortune LIKE :searchTerm');
$this->addFortuneCookieJoinAndSelect($qb);
return $qb
->setParameter('searchTerm', '%'.$term.'%')
->getQuery()
->execute();
}
... lines 40 - 66

And one more time in findWithFortunesJoin(). Remove the duplication, create a $qb variable, return the last part of the query, and stick our magic line in the middle::

... lines 1 - 39
public function findWithFortunesJoin($id)
{
$qb = $this->createQueryBuilder('cat')
->andWhere('cat.id = :id');
$this->addFortuneCookieJoinAndSelect($qb);
return $qb
->setParameter('id', $id)
->getQuery()
->getOneOrNullResult();
}
... lines 52 - 66

Try it! Refresh and click into a category. It all works. And you know, I feel a lot better. If there's one things I don't want to duplicate, it's query logic. I hope this looks really obvious to you - it's just a simple coding technique. But it's kind of amazing, because it's not something you can do easily with string queries. And it can really save you if once you've got complex WHERE clauses that need to be re-used. You don't want to screw that stuff up.

Leave a comment!

19
Login or Register to join the conversation
Duilio P. Avatar
Duilio P. Avatar Duilio P. | posted 3 years ago

Hello! I replaced the createQueryBuilder method in my repository class in order to return my own QueryBuilder object and add new methods to it:


/**
* @return MyQueryBuilder
*/
public function createQueryBuilder($alias, $indexBy = null)
{
return (new MyQueryBuilder($this->_em))
->select($alias)
->from($this->_entityName, $alias, $indexBy);
}


class MyQueryBuilder extends DoctrineQueryBuilder
{
public function addSubSelect(DoctrineQueryBuilder $queryBuilder, string $alias)
{
$this->addSelect(sprintf(
'(%s) as %s',
$queryBuilder->getDQL(), $alias
));

return $this;
}
}

Now in my repository class I can write:



return $this->createQueryBuilder('s')
->select('s.country')
->addSubSelect($numberOfPayments, 'num_payments')
->addSubselect($totalAmount, 'amount')
->groupBy('s.country')
->getQuery()
->getResult();

Where $numberOfPayments and $totalAmount are other query builder objects I'm passing as parameters.

This seems to work OK, but I was wondering if it could be considered a good practice / idea :)

1 Reply

Hey Duilio Palacios

Ha! that's clever but I don't think you need to create your own "QueryBuilder" class and override the create method. You can add a method like this to your repository and it should just work as the same but cleaner


// yourRepository.php
use Doctrine\ORM\QueryBuilder;

public function addSubSelect(QueryBuilder $queryBuilder, $alias)
{
$queryBuilder->addSelect(sprintf(
'(%s) as %s',
$queryBuilder->getDQL(), $alias
));
}

public function yourCustomMethod()
{
return $this->createQueryBuilder('s')
->select('s.country')
->addSubSelect($numberOfPayments, 'num_payments')
->addSubselect($totalAmount, 'amount')
->groupBy('s.country')
->getQuery()
->getResult();
}

Cheers!

1 Reply
Duilio P. Avatar

Hello! Thanks for your reply. The code above didn't work in Doctrine 2.6. I only managed to make it work by adding more custom code haha. I've shared it below. I'd prefer to keep the custom query builder methods in the custom query builder most of the time, but this was an interesting proof of concept.

https://gist.github.com/sil...

Reply

Ohh, right! My code is wrong. You cannot chain the calls like that because the "addSubSelect" method exists only in the repository, silly me :)

> I'd prefer to keep the custom query builder methods in the custom query builder most of the time, but this was an interesting proof of concept.
Cool, thanks for sharing it

Cheers!

Reply

Hello!! Thanks a lot for this awesome course! I would be grateful if you could please take a look at my issue!
I am creating a form but some field should be filled directly regarding to the user information from another database (SOAP server).

My question is what should I use to autofill them. I know it's a lazy question. I really need just a tip. Because I am searching about that but I need some help. Thanks again and really appreciate your support!

Reply

Hey Lubna,

It seems like you can't link the form type to a specific entity because information is stored in different databases... If so, then you can skip linking the form to a specific entity that will automatically link the form to an array, i.e. with $form->getData() you will get a simple PHP array. Then you will need to save the information from that array to different databases. Another common use case - create a "model" class for your form type with all the links you need and then link the form type to it. But that's in case you want to the form gives you an object where you can call getters later. The first option might be simpler for you I think. Just get the form data as plain PHP array and then do whatever you want with those data like set some of them on an entity, or execute some specific queries to any DB you need, or whatever :)

Cheers!

1 Reply

Victor Bocharsky
Thank you very much! I did it :)

Reply

Hey Lubna,

Great! I'm happy to hear it worked for you :)

Cheers!

1 Reply
Default user avatar

Hello,

I noticed a problem here and I have a question. Can we imagine that we have 20 methods in this class? And all of them use this private method so we do not have duplication.
And all works fine. But after 6 months, we need to change something, to remove something from private method. (better: a new developer needs to change this) So, we must look at every 20 methods and think whether it will cause an issue in 20 different places in app. I want to change only one method for 1 min without thinking whether I will break something in the rest of the application. So, this is my opinion.

Reply

Hey!

I think that's fair - this could be said about any re-use: if you re-use less (e.g. keep code inside one function instead of creating another), then you can refactor that function later without side effects. If you abstract and call an outside function, then you indeed *do* need to be more careful when refactoring that private function. Of course, at least these are *private* - that drastically reduces the scope of places you need to look for side effects from changes :).

Cheers!

Reply
Default user avatar
Default user avatar Fabian Picone | posted 4 years ago

UUh your code smells "addFortuneCookieJoinAndSelect". Never use "and" or "or" in Method names.

Reply

Hey Fabian Picone

Technically you are correct but sometimes you just want to do a couple of things in the same method, so I would say that that's ok to do it, but, you should refactor it as soon as you get one or two use cases that requires that action to be splitted.

Cheers!

Reply
Default user avatar
Default user avatar Fabian Picone | MolloKhan | posted 4 years ago

When you want to do a couple of things in a method, the smell begins. So split the "things" out in single methods. A method should do only one thing. Its more testable.

2 Reply
Duilio P. Avatar

I know this discussion is old but wanted to comment that I think the rule applies when the method does perform 2 different tasks, for example buildQueryAndSendEmail. In this case the method does only one thing (it adds more instructions to the query).

Reply

Hey Fabian,

Yes, I mostly agree with you, and in practice we do exactly this. But hey, this addFortuneCookieJoinAndSelect() method is so simple and has just one line of code ;)

Cheers!

Reply
Default user avatar
Default user avatar Fabian Picone | posted 4 years ago

Please? This is only my opinion in a discussion. I dont want to bash anyone. When you want more explanation then ask. This is really not constructive from you.
Code smell is a used term, see https://en.wikipedia.org/wi.... Its bad to use "and" or "or" in the method name because, its confusing on writing test. When you want to test a method you describe e.g. "testAddFortuneCookieJoinAndSelect", namely you are saying to test two things. But you should test only one thing per once. So a method should do only one thing and describe only this thing in the name.

Reply

Hey Fabian,

We're sorry about it, I moderated that comment. Not sure why this angers Timothy so much :/

Once again, I agree with you, and you have a good point. However, in practice, I think that's ok to use "and"/"or" when we're talking about 2 actions that do not exist without each other. Because in tests you need to test both of them and in a proper order too. Sometimes you can split into 2 private methods and call them from the public one, that makes sense if there's some complex logic and a lot of code for both actions.

Cheers!

Reply
Peter Avatar

One thing that happened to me, when I referenced the "QueryBuilder" and did the auto complete, it actually grabbed the DBAL one not the ORM one, just something to watch out for. :)

Reply

Good call! It's one of those annoying times when there are TWO of a given class!

1 Reply
Cat in space

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

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

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