Buy

Query Logic Re-use & Shortcuts

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:

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!

  • 2018-07-30 Victor Bocharsky

    Hey ssdk86 ,

    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!

  • 2018-07-28 ssdk86

    "....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...?

  • 2018-07-11 Diego Aguiar

    Great! so it's autocompleting now?

  • 2018-07-11 bartek1234321

    Yes I've got it ;)

  • 2018-06-27 Diego Aguiar

    Do you have the "PHP annotations" plugin installed?

  • 2018-06-26 bartek1234321

    I've got this but don't work.

  • 2018-06-26 Diego Aguiar

    Hey bartek1234321

    Try adding PHPDoc return type to that method


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

    Cheers!

  • 2018-06-26 bartek1234321

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