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

Symfony 6 >

COURSE

Doctrine, Symfony 6 & the Database

Buy Access to Course

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

The /browse page is working... but what if we click on one of these genres? Well... that kind of works. It shows the name of the genre... but we get a list of all the mixes. What we really want is to filter these to only show mixes for that specific genre.

Right now, every mix in the database is in the "Pop" genre. Head back to MixController and find the fake method that creates new mixes so that we can make some more interesting dummy data. Add a $genres variable with "Pop" and "Rock" included... Then select a random one with $genres[array_rand($genres)].

34 lines src/Controller/MixController.php
... lines 1 - 10
class MixController extends AbstractController
{
... line 13
public function new(EntityManagerInterface $entityManager): Response
{
... lines 16 - 18
$genres = ['pop', 'rock'];
$mix->setGenre($genres[array_rand($genres)]);
... lines 21 - 31
}
}

Cool! Now go to /mix/new and refresh a few times... until we have about 15 mixes. Back on /browse... yup! We have a mix of "Rock" and "Pop" genres... they just don't filter yet.

So our mission is clear: customize the database query to only return the results for a specific genre. Ok, we can actually do that super easily in VinylController via the findBy() method. The genre is in the URL as the $slug wildcard.

So we could add an "if" statement where, if there is a genre, we return all the results where genre matches $slug. But this is a great opportunity to learn how to create a custom query. So let's undo that.

Custom Repository Method

The best way to make a custom query, is to create a new method in the repository for whatever entity you're fetching data for. In this case, that means VinylMixRepository. This holds a few example methods. Un-comment the first... and then start simple.

67 lines src/Repository/VinylMixRepository.php
... lines 1 - 16
class VinylMixRepository extends ServiceEntityRepository
{
... lines 19 - 41
/**
* @return VinylMix[] Returns an array of VinylMix objects
*/
public function findByExampleField($value): array
{
return $this->createQueryBuilder('v')
->andWhere('v.exampleField = :val')
->setParameter('val', $value)
->orderBy('v.id', 'ASC')
->setMaxResults(10)
->getQuery()
->getResult()
;
}
... lines 56 - 65
}

Call it findAllOrderedByVotes(). We won't worry about the genre quite yet: I just want to make a query that returns all of the mixes ordered by votes. Remove the argument, this will return an array and the PHPdoc above helps my editor know that this will be an array of VinylMix objects

64 lines src/Repository/VinylMixRepository.php
... lines 1 - 41
/**
* @return VinylMix[] Returns an array of VinylMix objects
*/
public function findAllOrderedByVotes(): array
{
... lines 47 - 51
}
... lines 53 - 64

DQL and the QueryBuilder

There are a few different ways to execute a custom query in Doctrine. Doctrine, of course, eventually makes SQL queries. But Doctrine works with MySQL, Postgres and other database engines... and the SQL needed for each of those looks slightly different.

To handle this, internally, Doctrine has its own query language called Doctrine Query Language or "DQL", It looks something like:

SELECT v FROM App\Entity\VinylMix v WHERE v.genre = 'pop';

You can write these strings by hand, but I leverage Doctrine's "QueryBuilder": a nice object that helps... ya know... build that query!

Creating the QueryBuilder

To use it, start with $this->createQueryBuilder() and pass an alias that will be used to identify this class within the query. Make this short, but unique among your entities - something like mix.

64 lines src/Repository/VinylMixRepository.php
... lines 1 - 44
public function findAllOrderedByVotes(): array
{
return $this->createQueryBuilder('mix')
... lines 48 - 51
}
... lines 53 - 64

Because we're calling this from inside of VinylMixRepository, the QueryBuilder already knows to query from the VinylMix entity... and will use mix as the alias. If we executed this query builder right now, it would basically be:

SELECT * FROM vinyl_mix AS mix

The query builder is loaded with methods to control the query. For example, call ->orderBy() and pass mix - since that's our alias - .votes then DESC.

64 lines src/Repository/VinylMixRepository.php
... lines 1 - 44
public function findAllOrderedByVotes(): array
{
return $this->createQueryBuilder('mix')
->orderBy('mix.votes', 'DESC')
... lines 49 - 51
}
... lines 53 - 64

Done! Now that our query is built, to execute call ->getQuery() (that turns it into a Query object) and then ->getResult().

64 lines src/Repository/VinylMixRepository.php
... lines 1 - 44
public function findAllOrderedByVotes(): array
{
return $this->createQueryBuilder('mix')
... line 48
->getQuery()
->getResult()
;
}
... lines 53 - 64

Well actually, there are a number of methods you can call to get the results. The main two are getResult() - which returns an array of the matching objects - or getOneOrNullResult(), which is what you would use if you were querying for one specific VinylMix or null. Because we want to return an array of matching mixes, use getResult().

Now we can use this method. Over in VinylController (let me close MixController...), instead of findBy(), call findAllOrderedByVotes().

49 lines src/Controller/VinylController.php
... lines 1 - 10
class VinylController extends AbstractController
{
... lines 13 - 36
public function browse(VinylMixRepository $mixRepository, string $slug = null): Response
{
... lines 39 - 40
$mixes = $mixRepository->findAllOrderedByVotes();
... lines 42 - 46
}
}

I love how clear that method is: it makes it super obvious exactly what we're querying for. And when we try it... it still works! It's not filtering yet, but the order is correct.

Adding the WHERE Statement

Okay, back to our new method. Add an optional string $genre = null argument. If a genre is passed, we need to add a "where" statement. To make space for that, break this onto multiple lines... and replace return with $queryBuilder =. Below, return $queryBuilder with ->getQuery(), and ->getResult().

66 lines src/Repository/VinylMixRepository.php
... lines 1 - 16
class VinylMixRepository extends ServiceEntityRepository
{
... lines 19 - 44
public function findAllOrderedByVotes(string $genre = null): array
{
$queryBuilder = $this->createQueryBuilder('mix')
->orderBy('mix.votes', 'DESC');
return $queryBuilder
->getQuery()
->getResult()
;
}
... lines 55 - 64
}

Now we can say if ($genre), and add the "where" statement. How? I bet you could guess: $queryBuilder->andWhere().

But a word of warning. There is also a where() method... but I never use it. When you call where(), it will clear any existing "where" statements that the query builder might have... so you might accidentally remove something you added earlier. So, always use andWhere(). Doctrine is smart enough to figure out that, because this is the first WHERE, it doesn't actually need to add the AND.

Inside of andWhere(), pass mix.genre =... but don't put the dynamic genre right in the string. That is a huge no-no: never do that. That opens you up for SQL injection attacks. Instead, whenever you need to put a dynamic value into a query, use a "prepared statement"... which is a fancy way of saying that you put a placeholder here, like :genre. The name of this could be anything... like "dinosaur" if you want. But whatever you call it, you'll then fill in the placeholder by saying ->setParameter() with the name of the parameter - so genre - and then the value: $genre.

71 lines src/Repository/VinylMixRepository.php
... lines 1 - 44
public function findAllOrderedByVotes(string $genre = null): array
{
... lines 47 - 49
if ($genre) {
$queryBuilder->andWhere('mix.genre = :genre')
->setParameter('genre', $genre);
}
... lines 54 - 58
}
... lines 60 - 71

Beautiful! Back over in VinylController, pass $slug as the genre.

Let's try this! Click back to the browse page first. Awesome! We get all the results. Now click "Rock" and... nice! Less results and all genres show "Rock"! If I filter by "Pop"... got it! We can even see the query for this... here it is. It has the "where" statement for genre equaling "Pop". Woo!

Reusing Query Builder Logic

As your project gets bigger and bigger, you're going to create more and more methods in your repository for custom queries. And you may start repeating the same query logic over and over again. For example, we might order by the votes in a bunch of different methods in this class.

To avoid duplication, we can isolate that logic into a private method. Check it out! Add private function addOrderByVotesQueryBuilder(). This will accept a QueryBuilder argument (we want the one from Doctrine\ORM), but let's make it optional. And we will also return a QueryBuilder.

78 lines src/Repository/VinylMixRepository.php
... lines 1 - 17
class VinylMixRepository extends ServiceEntityRepository
{
... lines 20 - 60
private function addOrderByVotesQueryBuilder(QueryBuilder $queryBuilder = null): QueryBuilder
{
... lines 63 - 65
}
... lines 67 - 76
}

The job of this method is to add this ->orderBy() line. And for convenience, if we don't pass in a $queryBuilder, we'll create a new one.

To allow that, start with $queryBuilder = $queryBuilder ?? $this->createQueryBuilder('mix'). I'm purposely using mix again for the alias. To keep life simple, choose an alias for an entity and consistently use it everywhere.

78 lines src/Repository/VinylMixRepository.php
... lines 1 - 60
private function addOrderByVotesQueryBuilder(QueryBuilder $queryBuilder = null): QueryBuilder
{
$queryBuilder = $queryBuilder ?? $this->createQueryBuilder('mix');
... lines 64 - 65
}
... lines 67 - 78

Anyways, this line itself may look weird, but it basically says:

If there is a QueryBuilder, then use it. Else, create a new one.

Below return $queryBuilder... go steal the ->orderBy() logic from up here and... paste. Awesome!

78 lines src/Repository/VinylMixRepository.php
... lines 1 - 60
private function addOrderByVotesQueryBuilder(QueryBuilder $queryBuilder = null): QueryBuilder
{
... lines 63 - 64
return $queryBuilder->orderBy('mix.votes', 'DESC');
}
... lines 67 - 78

PhpStorm is a little angry with me... but that's just because it's having a rough morning and needs a restart: our code is, hopefully, just fine.

Back up in the original method, simplify to $queryBuilder = $this->addOrderByVotesQueryBuilder() and pass it nothing.

78 lines src/Repository/VinylMixRepository.php
... lines 1 - 45
public function findAllOrderedByVotes(string $genre = null): array
{
$queryBuilder = $this->addOrderByVotesQueryBuilder();
... lines 49 - 58
}
... lines 60 - 78

Isn't that nice? When we refresh... it's not broken! Take that PhpStorm!

Next, let's add a "mix show" page where we can view a single vinyl mix. For the first time, we'll query for a single object from the database and deal with what happens if no matching mix is found.

Leave a comment!

Most Useful Newest Oldest
12
Login or Register to join the conversation
Akili Avatar

Hey SymfonyCast!

I had a question about querying images from the database. Would it be better to make URL requests for images statically from api's or is it better to query images from the database? And yes, I've learned that Doctrine queries the database in the background; with the focus on instantiating objects not tables.

Reply
MolloKhan Avatar

Hey @Akili

It's better only to store info about the image in the database, for example their filename, render an img tag pointing to the file, and let the browser request them.

Cheers!

Reply
DimitarIsusov Avatar
DimitarIsusov Avatar DimitarIsusov | posted 1 month ago | edited

There is a typo at 3:50 in the query "vinly_mix"

Reply
MolloKhan Avatar

Wow, nice catch! Thank you for reporting it

1 Reply
Markchicobaby Avatar
Markchicobaby Avatar Markchicobaby | posted 9 months ago

I'm wondering how the setParameter method makes the subsequent SQL safer. Does it do something to parse the string to clean it up, or does it use native SQL features to pass the parameters (and hence let the database worry about wrapping the parameter)?

        $queryBuilder->andWhere('mix.genre = :genre')
            ->setParameter('genre', $genre);
Reply
sadikoff Avatar

Hi,

Yeah, the doctrine will cover you with parameters so it is safe just pass it to ->setParameter(). I can't describee how exactly it works internally, Doctrinie code is not so cool to explore

Cheers

Reply
Eric Avatar

How is it possible to match the aliases with a QueryBuilder provided by a third party?
`

private function addOrderByVotesQueryBuilder(QueryBuilder $queryBuilder = null): QueryBuilder
{
    $queryBuilder = $queryBuilder ?? $this->createQueryBuilder('mix');

}

`
For example I'm trying to reuse a QueryBuilder with this design in EasyAdmins createIndexQueryBuilder(). But EasyAdmin uses a generic 'entity' as its alias. Because I am passing parent::createIndexQueryBuilder(...) to my QueryBuilder the aliases do not match up. Is there any solution?

Reply
sadikoff Avatar

Hey,

You can get an alias from QueryBuilder object with ->getRootAliases() it will return array of aliases and first element will be your alias!

Cheers!

1 Reply
Eric Avatar

I read about that method but wasn't sure wich key would be safe to use. If first key is always the entity key, then a huge "Thanks" for pointing that out. Appreciate the quick response!

Reply
sadikoff Avatar

As I know the first key is safe to use as entity key. I didn't heard about another behaviour.

Cheers!

Reply
Rufnex Avatar
Rufnex Avatar Rufnex | posted 10 months ago | edited

Wouldn't a method like the following be more flexible?

private function addOrderByVotesQueryBuilder(QueryBuilder $queryBuilder = null, $column = null): QueryBuilder
{
    $queryBuilder = $queryBuilder ?? $this->createQueryBuilder('mix');
    $column = $column ?? 'mix.votes';

    return $queryBuilder->orderBy($column, 'DESC');
}

By the way, whats a better coding style for the null coalescing operator .. ?? or you use ?:

Reply
Victor Avatar

Hey Ruflex,

It depends on your use cases. If you need an additional control - passing the column as an argument to the method like you're doing is a good idea. But if you need that flexibility - better make it simple as in our case :) And as always, the simplest your code - the easier to maintain it for other people, or even for you in the future ;)

About "??" or "?:" - that's not about coding style actually because it's a different implementation. Read about both of them in the PHP docs to understand the difference. In short, if the var might not exist - you should use ??, but if it always exists but might just be null sometimes - ?: syntax is enough.

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": "*",
        "babdev/pagerfanta-bundle": "^3.7", // v3.7.0
        "doctrine/doctrine-bundle": "^2.7", // 2.7.0
        "doctrine/doctrine-migrations-bundle": "^3.2", // 3.2.2
        "doctrine/orm": "^2.12", // 2.12.3
        "knplabs/knp-time-bundle": "^1.18", // v1.19.0
        "pagerfanta/doctrine-orm-adapter": "^3.6", // v3.6.1
        "pagerfanta/twig": "^3.6", // v3.6.1
        "sensio/framework-extra-bundle": "^6.2", // v6.2.6
        "stof/doctrine-extensions-bundle": "^1.7", // v1.7.0
        "symfony/asset": "6.1.*", // v6.1.0
        "symfony/console": "6.1.*", // v6.1.2
        "symfony/dotenv": "6.1.*", // v6.1.0
        "symfony/flex": "^2", // v2.2.2
        "symfony/framework-bundle": "6.1.*", // v6.1.2
        "symfony/http-client": "6.1.*", // v6.1.2
        "symfony/monolog-bundle": "^3.0", // v3.8.0
        "symfony/proxy-manager-bridge": "6.1.*", // v6.1.0
        "symfony/runtime": "6.1.*", // v6.1.1
        "symfony/twig-bundle": "6.1.*", // v6.1.1
        "symfony/ux-turbo": "^2.0", // v2.3.0
        "symfony/webpack-encore-bundle": "^1.13", // v1.15.1
        "symfony/yaml": "6.1.*", // v6.1.2
        "twig/extra-bundle": "^2.12|^3.0", // v3.4.0
        "twig/twig": "^2.12|^3.0" // v3.4.1
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.4", // 3.4.2
        "symfony/debug-bundle": "6.1.*", // v6.1.0
        "symfony/maker-bundle": "^1.41", // v1.44.0
        "symfony/stopwatch": "6.1.*", // v6.1.0
        "symfony/web-profiler-bundle": "6.1.*", // v6.1.2
        "zenstruck/foundry": "^1.21" // v1.21.0
    }
}
Edit on GitHub
Previous Chapter
Next Chapter
userVoice