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

Symfony 4 >

COURSE

Doctrine & the Database in Symfony 4

Buy Access to Course
This tutorial has a new version, check it out!

Query Logic Re-use & Shortcuts

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

One of my favorite things about the query builder is that, with a few tricks, you can reuse query logic! Check this out: right now, we only have one custom method in ArticleRepository:

47 lines src/Repository/ArticleRepository.php
... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 21
/**
* @return Article[]
*/
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
->orderBy('a.publishedAt', 'DESC')
->getQuery()
->getResult()
;
}
... lines 34 - 45
}

But, as our app grows, we'll certainly need to add more. And there's a pretty darn good chance that another custom query will also need to filter its results to only show published articles. In a perfect world, we would share that logic, between both custom methods. And... we can do that!

Step 1 is to isolate the query logic that we need to share into its own private method. At the bottom, create a private function addIsPublishedQueryBuilder() with a QueryBuilder type-hint - the one from Doctrine\ORM - and $qb:

54 lines src/Repository/ArticleRepository.php
... lines 1 - 6
use Doctrine\ORM\QueryBuilder;
... lines 8 - 15
class ArticleRepository extends ServiceEntityRepository
{
... lines 18 - 48
private function addIsPublishedQueryBuilder(QueryBuilder $qb)
{
... line 51
}
}

Next, go up, copy that part of the query, and just return $qb->andWhere('a.publishedAt IS NOT NULL'):

54 lines src/Repository/ArticleRepository.php
... lines 1 - 15
class ArticleRepository extends ServiceEntityRepository
{
... lines 18 - 48
private function addIsPublishedQueryBuilder(QueryBuilder $qb)
{
return $qb->andWhere('a.publishedAt IS NOT NULL');
}
}

And since we're returning this - and each query builder method returns itself - back up top, we can say $qb = $this->createQueryBuilder('a'), and below, return $this->addIsPublishedQueryBuilder() passing it $qb:

54 lines src/Repository/ArticleRepository.php
... lines 1 - 15
class ArticleRepository extends ServiceEntityRepository
{
... lines 18 - 25
public function findAllPublishedOrderedByNewest()
{
$qb = $this->createQueryBuilder('a');
return $this->addIsPublishedQueryBuilder($qb)
->orderBy('a.publishedAt', 'DESC')
->getQuery()
->getResult()
;
}
... lines 36 - 52
}

The rest of the query can chain off of this.

And... that's it! One important note is that you need to consistently use the same alias, like a, across all of your methods.

Fancier Re-Use

This is nice... but since I do this a lot, we can get a bit fancier. Create another private method called getOrCreateQueryBuilder() with a QueryBuilder argument like before, but make it optional:

58 lines src/Repository/ArticleRepository.php
... lines 1 - 6
use Doctrine\ORM\QueryBuilder;
... lines 8 - 15
class ArticleRepository extends ServiceEntityRepository
{
... lines 18 - 52
private function getOrCreateQueryBuilder(QueryBuilder $qb = null)
{
... line 55
}
}

Here's the idea: when someone calls this method, if the query builder is passed, we'll just return it. Otherwise we will return a new one with $this->createQueryBuilder('a'):

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

If you're not used to this syntax, it means that if a QueryBuilder object is passed, return that QueryBuilder object. If a QueryBuilder object is not passed, then create one.

This is cool, because now we can make the argument to addIsPublishedQueryBuilder() also optional:

58 lines src/Repository/ArticleRepository.php
... lines 1 - 15
class ArticleRepository extends ServiceEntityRepository
{
... lines 18 - 46
private function addIsPublishedQueryBuilder(QueryBuilder $qb = null)
{
... lines 49 - 50
}
... lines 52 - 56
}

Inside, use the new method: return $this->getOrCreateQueryBuilder() passing it $qb, and then our andWhere():

58 lines src/Repository/ArticleRepository.php
... lines 1 - 15
class ArticleRepository extends ServiceEntityRepository
{
... lines 18 - 46
private function addIsPublishedQueryBuilder(QueryBuilder $qb = null)
{
return $this->getOrCreateQueryBuilder($qb)
->andWhere('a.publishedAt IS NOT NULL');
}
... lines 52 - 56
}

But the real beautiful thing is back up top. This whole method can now be one big chained call: return $this->addIsPublishedQueryBuilder() - and pass nothing:

58 lines src/Repository/ArticleRepository.php
... lines 1 - 15
class ArticleRepository extends ServiceEntityRepository
{
... lines 18 - 25
public function findAllPublishedOrderedByNewest()
{
return $this->addIsPublishedQueryBuilder()
->orderBy('a.publishedAt', 'DESC')
->getQuery()
->getResult()
;
}
... lines 34 - 56
}

It will create the QueryBuilder for us.

So not only do we have really nice public functions for fetching data, we also have some private functions to help us build our queries. Let's make sure it works. Find your browser and, refresh! It still looks good!

ParamConverter: Automatically Querying

Ok, enough custom queries for now. Instead, I want to show you a query shortcut!

Go to ArticleController and find the show() action. Sometimes you need to query for an array of objects. So, we get the repository, call some method, and, done!

81 lines src/Controller/ArticleController.php
... lines 1 - 5
use App\Repository\ArticleRepository;
... lines 7 - 16
class ArticleController extends AbstractController
{
... lines 19 - 31
public function homepage(ArticleRepository $repository)
{
$articles = $repository->findAllPublishedOrderedByNewest();
... lines 35 - 38
}
... lines 40 - 79
}

Life is good. But it's also really common to query for just one object. And in these situations, if the query you need is simple... you can make Symfony do all of the work:

81 lines src/Controller/ArticleController.php
... lines 1 - 16
class ArticleController extends AbstractController
{
... lines 19 - 43
public function show($slug, SlackClient $slack, EntityManagerInterface $em)
{
... lines 46 - 50
/** @var Article $article */
$article = $repository->findOneBy(['slug' => $slug]);
... lines 53 - 66
}
... lines 68 - 79
}

Let me show you: remove the $slug argument and replace it with Article $article:

74 lines src/Controller/ArticleController.php
... lines 1 - 4
use App\Entity\Article;
... lines 6 - 16
class ArticleController extends AbstractController
{
... lines 19 - 40
/**
* @Route("/news/{slug}", name="article_show")
*/
public function show(Article $article, SlackClient $slack)
{
... lines 46 - 59
}
... lines 61 - 72
}

Then, below, because I removed the $slug argument, use $article->getSlug():

74 lines src/Controller/ArticleController.php
... lines 1 - 4
use App\Entity\Article;
... lines 6 - 16
class ArticleController extends AbstractController
{
... lines 19 - 40
/**
* @Route("/news/{slug}", name="article_show")
*/
public function show(Article $article, SlackClient $slack)
{
if ($article->getSlug() === 'khaaaaaan') {
... line 47
}
... lines 49 - 59
}
... lines 61 - 72
}

We can also remove all of the query, and even the 404 logic:

74 lines src/Controller/ArticleController.php
... lines 1 - 4
use App\Entity\Article;
... lines 6 - 16
class ArticleController extends AbstractController
{
... lines 19 - 40
/**
* @Route("/news/{slug}", name="article_show")
*/
public function show(Article $article, SlackClient $slack)
{
if ($article->getSlug() === 'khaaaaaan') {
$slack->sendMessage('Kahn', 'Ah, Kirk, my old friend...');
}
$comments = [
'I ate a normal rock once. It did NOT taste like bacon!',
'Woohoo! I\'m going on an all-asteroid diet!',
'I like bacon too! Buy some from my site! bakinsomebacon.com',
];
return $this->render('article/show.html.twig', [
'article' => $article,
'comments' => $comments,
]);
}
... lines 61 - 72
}

Before we talk about this, move over and click on one of the articles. Yea! Somehow, this totally works! Back in our code, we can remove the unused EntityManagerInterface argument:

74 lines src/Controller/ArticleController.php
... lines 1 - 4
use App\Entity\Article;
... lines 6 - 7
use App\Service\SlackClient;
... lines 9 - 16
class ArticleController extends AbstractController
{
... lines 19 - 43
public function show(Article $article, SlackClient $slack)
{
... lines 46 - 59
}
... lines 61 - 72
}

Here's the deal. We already know that if you type-hint, a service, Symfony will pass you that service. In addition to that, if you type-hint an entity class, Symfony will automatically query for that entity. How? It looks at all of the route's placeholder values - which is just one in this case, {slug} - and creates a query where the slug field matches that value:

Tip

It requires sensio/framework-extra-bundle to be installed in order to automatically query for entity objects

74 lines src/Controller/ArticleController.php
... lines 1 - 16
class ArticleController extends AbstractController
{
... lines 19 - 40
/**
* @Route("/news/{slug}", name="article_show")
*/
public function show(Article $article, SlackClient $slack)
{
... lines 46 - 59
}
... lines 61 - 72
}

In other words, to use this trick, your routing wildcard must be named the same as the property on your entity, which is usually how I do things anyways. It executes the exact same query that we were doing before by hand! If there is not a slug that matches this, it also automatically throws a 404, before the controller is ever called.

In fact, try that - put in a bad slug. Yep, error! Something about the Article object not found by the @ParamConverter annotation. So, that's not a great error message - it makes more sense if you know that the name of this feature internally is ParamConverter.

So... yea! If you organize your route wildcards to match the property on your entity, which is a good idea anyways, then you can use this trick. If you need a more complex query, no problem! You can't use this shortcut, but it's still simple enough: autowire the ArticleRepository, and then call whatever method you need.

Leave a comment!

Most Useful Newest Oldest
38
Login or Register to join the conversation
Sander H. Avatar
Sander H. Avatar Sander H. | posted 4 years ago

It took me a while to figure out that the Entity autowiring requires sensio/framework-extra-bundle.

1 Reply
MolloKhan Avatar

Hey Sander H.

Why was that? Do you think we missed something?

Cheers!

Reply
Sander H. Avatar

Well, I was using the tutorials to make a project of my own. In the sample code, the composer.json contains the sensio/framework-extra-bundle, but my project didn't.

In the text of this video, it says:

"In addition to that, if you type-hint an entity class, Symfony will automatically query for that entity."

Maybe you could rewrite it to something along the lines of:

"In addition to that, if you type-hint an entity class, (and if you have sensio/framework-extra-bundle installed) Symfony will automatically query for that entity."

Because, if I understand it correctly, it is not a core feature of Symfony.

1 Reply
MolloKhan Avatar

Ohh, I understand it now. You're right it's not part of the core of Symfony but it comes by default when you install a fresh web version of Symfony.
Check it out: https://github.com/symfony/...
Anyways, I'll talk about it internally with the staff

Cheers!

Reply
miljan-knezevic-developer Avatar

Hey, great tutorials, awesome job! :)

But I must report that this video fails to load with the message: "The media could not be loaded, either because the server or network failed or because the format is not supported."
I've tried with both firefox and chrome, it fails consistently.

Please, try to fix it.
Thanks in advance and keep up with the great work! :)

Reply
Victor Avatar

Hey Miljan,

We're sorry about it and thank you for reporting this! Do you have such problem with only *this* video or every video on our website? If only this one - this sounds like a temporary problem that might cause with problems on Vimeo side - the platform we use to host our videos. I just double-checked and this video works for me fine, please, try again and let us know if you still have this problem. Btw, sometimes refreshing the page a few times might help to solve this issue. Or try to open the video in another browser or Chrome Incognito mode.

Cheers!

Reply
Akavir S. Avatar
Akavir S. Avatar Akavir S. | posted 3 years ago | edited

Is it not easier to do?

`

public function FindAllPublishedOrderByNewest()
{

return $this->FindAllPublished()
    ->orderBy('article.publishedAt', 'DESC')
    ->getQuery()
    ->getResult()
;

}

private function FindAllPublished()
{

return $this->createQueryBuilder('article')
    ->andWhere('article.publishedAt IS NOT NULL');

}`

Cheers! :p

Reply
Victor Avatar

Hey Virgile,

Easier in your example, but not so flexible :) What if you would like to apply a few logics that you separated with a few private methods, like find "all published" but also for the "specific author". If you would call createQueryBuilder() in both private methods - you can't reuse them in FindAllPublishedOrderByNewest(). That's why apply only WHERE clouses in private reusable methods is more flexible as you're not blocking it with createQueryBuilder().

I hope this makes sense for you :)

Cheers!

1 Reply
Akavir S. Avatar

Hey Victor,

Thanks you a lot for the time you took to answer me !

It is now more clear to me !

Cheers !

Reply
Victor Avatar

Hey Virgile,

Glad it helped!

Cheers!

1 Reply
Dung L. Avatar
Dung L. Avatar Dung L. | posted 3 years ago

I am not sure why i do not get a notification for a reply on this message board, and I can't remember what where i posted my question to revisit. Kindly thanks!

Reply
Victor Avatar

Hey Dung,

It's Disqus comments block, you can find your latest comments in the right sidebar on this page, click on your name in the right top corner of this Disqus block, then choose "Profile". Or simply click on your avatar picture or full name in your comment. In the right popup sidebar you'll find "Comments" tab with your latest comments - the easiest way to find your latest comments. About notifications, I think you need to check disqus settings for your account.

I hope this helps!

Cheers!

Reply
Ryan S. Avatar
Ryan S. Avatar Ryan S. | posted 4 years ago

I've run into a show stopping issue. When pulling $slug out of injection and changing to Article $article then $article->getSlug(). I refresh my page and get a big ugly error that I cannot quite decipher myself.
Error: During class fetch: Uncaught ReflectionException: Class Symfony\Component\Form\FormTypeGuesserInterface not found in /var/www/symfony/the_spacebar/vendor/symfony/doctrine-bridge/Form/DoctrineOrmTypeGuesser.php:25 Stack trace: #0 /var/www/symfony/the_spacebar/vendor/symfony/debug/DebugClassLoader.php(156): require('/var/www/symfon...') #1 [internal function]: Symfony\Component\Debug\DebugClassLoader->loadClass('Symfony\\Bridge\\...') #2 [internal function]: spl_autoload_call('Symfony\\Bridge\\...') #3 /var/www/symfony/the_spacebar/vendor/symfony/config/Resource/ClassExistenceResource.php(78): class_exists('Symfony\\Bridge\\...') #4 /var/www/symfony/the_spacebar/vendor/symfony/dependency-injection/ContainerBuilder.php(353): Symfony\Component\Config\Resource\ClassExistenceResource->isFresh(0) #5 /var/www/symfony/the_spacebar/vendor/symfony/dependency-injection/Compiler/AutowirePass.php(336): Symfony\Component\DependencyInjection\ContainerBuilder->getReflectionClass('Symfony\\Bridge\\...', false) #6 /var/www/symf

Any thoughts on this?

Reply
Ryan S. Avatar

seems to be an active bug with this: https://github.com/symfony/...

I had to:
composer require validator
composer require symfony/form
I also did
composer require symfony/phpunit-bridge
but i'm not sure if that one was needed.

Just FYI in case anyone else encounters this with PHP 7.2/7.3/7.4

Reply
Victor Avatar

Hey Ryan S. ,

Yes, you're right! It seems like you were found the correct issue on GitHub. For now, you have a few options how to solve this:

1. Temporarily install missing dependencies that causes this error. It looks like in this case just execute "composer require forms"
2. Downgrade your PHP version. Affected version are 7.3.7 and 7.2.20 as you can see in the issue, so you should avoid them
3. Wait for 7.2.21 / 7.3.8 release and upgrade your PHP version

Cheers!

1 Reply
Ryan S. Avatar

I required validator too before it would work.
I know i could downgrade PHP or wait and upgrade PHP but it sounded like from that github convo that this change in PHP handling of the error is going to come one way or another at some point. I find it odd that if doctrine actually requires these packages why they aren't installed as dependencies at time of install?

Reply
Victor Avatar

Hey Ryan S.,

> I find it odd that if doctrine actually requires these packages why they aren't installed as dependencies at time of install?

Some dependencies are optional, and you need to install them only if you use some specific features. It allows to keep library dependencies slim. But due to that BC break some things were broken. Anyway, let's follow that threat for now to track the problem and its implementation.

Cheers!

Reply
MolloKhan Avatar

Hey man, can you try it out on PHP 7.1? There is a bug between Symfony and PHP latest version

Cheers!

Reply
Ryan S. Avatar

I'm sure that's the issue, I just don't want to downgrade my PHP to test. when i included all the required dependencies that I listed it worked.

Reply
MolloKhan Avatar

Ohh, that's interesting. Can you tell me on which Symfony version are you running?

Reply
Ryan S. Avatar

4.3.2

Reply
Dominik Avatar
Dominik Avatar Dominik | posted 4 years ago

Two small questions about that "slug" placeholder. What will happen if my route would be looking like this: "article/{id}/{slug}". How can I tell symfony which placeholder should use to get a correct DB record?
Second question is. Why in this tutorial to identification article you using "title" not article "id"? I thought searching by id in DB is much quicker then string? Is not a better practise to have URL like this"article/{id}/{slug}" and have only "id" unique?

Reply
weaverryan Avatar

Hey Dominik!

Good questions!

What will happen if my route would be looking like this: "article/{id}/{slug}". How can I tell symfony which placeholder should use to get a correct DB record?

You have a few options. First, if you do nothing, it will try to use both columns - e.g. WHERE id = {id} AND slug = {slug}. In practice, that's probably fine, though technically using both is unnecessary.

To control it further, you can use a @ParamConverter annotation to tell Symfony which field to use https://symfony.com/doc/current/bundles/SensioFrameworkExtraBundle/annotations/converters.html#fetch-via-an-expression

Or, you can simply skip the "param conversion" and use the $id variable to manually query in your controller.

Why in this tutorial to identification article you using "title" not article "id"? I thought searching by id in DB is much quicker then string? Is not a better practise to have URL like this"article/{id}/{slug}" and have only "id" unique?

By "title" - do you mean the "slug"? You're right that we are querying via the slug (the "clean" article string) not the id. But, that is not slower than querying for id because of one important reason: In Article, above the slug property, we have unique=true. This creates unique index in the database, which means querying by slug is super fast.

Let me know if that makes sense!

Cheers!

1 Reply
Dominik Avatar

You are right. I forgot about index. Regards

Reply
Ahmed A. Avatar
Ahmed A. Avatar Ahmed A. | posted 4 years ago

is creating a function that getOrCreate just one object of the query builder can be considered a practice for the single tone pattern design?

Reply
Victor Avatar

Hey Ahmed,

Not really, singleton pattern means you design the class in such way that you cannot create more than one object of it - it's just impossible because you make its constructor private and the only way to create an object - use a specific public static method that implements some logic. But here we just create a helper method that implement this behavior, but you can easily bypass it - just cal create method directly instead of calling this getOrCreate() one. So, singleton pattern is something different :)

Cheers!

1 Reply
Ahmed A. Avatar

awesome! thank you :)

Reply
Victor Avatar

You're welcome ;)

Cheers!

1 Reply
Chuck norris Avatar
Chuck norris Avatar Chuck norris | posted 4 years ago

Hi,

Is there a way to change the "not found by the @ParamConverter annotation" message ?

Thanks.

Reply
MolloKhan Avatar

Hey Chuck norris

I'm not sure if that's easy to achieve but those messages are not visible by users when you are on production mode, they will see your 404 template instead

Cheers!

Reply
Aleksandr T. Avatar
Aleksandr T. Avatar Aleksandr T. | posted 5 years ago

"....Symfony will automatically query for that entity. How? It looks at all of the route's placeholder values - which is just one in this case, {slug} - and creates a query where the slug field matches that value:..."
Does this mean I should add column to index because Symfony create query like
"SELECT * FROM table WHERE table.slug LIKE '%slug%' "?
and if database is big without table.slug set as index it will be hard query...?

Reply
Victor Avatar

Hey Aleksandr T. ,

Yes, it's a good idea to add index for this field. Well, actually the "slug" field should be unique, and if you already made this fields unique - that means it's already indexed.

Cheers!

Reply
bartek Avatar

When I use this logic then phpstorm stops prompting orm class fields (eg inside addIsPublishedQueryBuilder function). Do I do anything wrong?

Reply
MolloKhan Avatar

Hey bartek

Try adding PHPDoc return type to that method


/**
* @return QueryBuilder
*/
privatefunction addIsPublishedQueryBuilder(QueryBuilder $qb) {...}

Cheers!

Reply
bartek Avatar

I've got this but don't work.

Reply
MolloKhan Avatar

Do you have the "PHP annotations" plugin installed?

Reply
bartek Avatar

Yes I've got it ;)

Reply
MolloKhan Avatar

Great! so it's autocompleting now?

Reply
Cat in space

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

Symfony 4 Doctrine 2

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.1.3",
        "ext-iconv": "*",
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "knplabs/knp-markdown-bundle": "^1.7", // 1.7.0
        "knplabs/knp-time-bundle": "^1.8", // 1.8.0
        "nexylan/slack-bundle": "^2.0,<2.2.0", // v2.0.0
        "php-http/guzzle6-adapter": "^1.1", // v1.1.1
        "sensio/framework-extra-bundle": "^5.1", // v5.1.4
        "stof/doctrine-extensions-bundle": "^1.3", // v1.3.0
        "symfony/asset": "^4.0", // v4.0.4
        "symfony/console": "^4.0", // v4.0.14
        "symfony/flex": "^1.0", // v1.17.6
        "symfony/framework-bundle": "^4.0", // v4.0.14
        "symfony/lts": "^4@dev", // dev-master
        "symfony/orm-pack": "^1.0", // v1.0.6
        "symfony/twig-bundle": "^4.0", // v4.0.4
        "symfony/web-server-bundle": "^4.0", // v4.0.4
        "symfony/yaml": "^4.0" // v4.0.14
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.0", // 3.0.2
        "easycorp/easy-log-handler": "^1.0.2", // v1.0.4
        "fzaninotto/faker": "^1.7", // v1.7.1
        "symfony/debug-bundle": "^3.3|^4.0", // v4.0.4
        "symfony/dotenv": "^4.0", // v4.0.14
        "symfony/maker-bundle": "^1.0", // v1.4.0
        "symfony/monolog-bundle": "^3.0", // v3.1.2
        "symfony/phpunit-bridge": "^3.3|^4.0", // v4.0.4
        "symfony/profiler-pack": "^1.0", // v1.0.3
        "symfony/var-dumper": "^3.3|^4.0" // v4.0.4
    }
}
Edit on GitHub
Previous Chapter
Next Chapter
userVoice