Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine

The QueryBuilder

Video not working?

It looks like your browser may not support the H264 codec. If you're using Linux, try a different browser or try installing the gstreamer0.10-ffmpeg gstreamer0.10-plugins-good packages.

Thanks! This saves us from needing to use Flash or encode videos in multiple formats. And that let's us get back to making more videos :). But as always, please feel free to message us.

It's really powerful to understand that DQL is ultimately what's being used behind the scenes in Doctrine. But most of the time, we're not going to build this DQL string by hand. Nope, we're going to use something called the "QueryBuilder". Ooooh.

Creating the QueryBuilder

Comment out the DQL. Let's rebuild this with the QueryBuilder. Start with $qb (for "QueryBuilder") = $this->createQueryBuilder(). Inside, say category.

... lines 1 - 17
class CategoryRepository extends ServiceEntityRepository
{
... lines 20 - 27
public function findAllOrdered(): array
{
... line 30
$qb = $this->createQueryBuilder('category')
... lines 32 - 35
}
... lines 37 - 79
}

Because we're inside CategoryRepository, when we say createQueryBuilder(), that automatically adds FROM App\Entity\Category and aliases it to category, since that's what we passed as the argument. This also selects everything by default. So... with just this, we've already recreated most of this query!

To add the next spot, you can chain off of this: ->addOrderBy() with category.name. Then I'll use this Criteria class ( hit "tab" to autocomplete that) followed by DESC. Or you could just put the string 'DESC': it's the same thing.

... lines 1 - 27
public function findAllOrdered(): array
{
... line 30
$qb = $this->createQueryBuilder('category')
->addOrderBy('category.name', Criteria::DESC);
... lines 33 - 35
}
... lines 37 - 81

Executing the QueryBuilder

QueryBuilder done! To execute it, we still need that Query object. Now we can get it with $qb->getQuery(). Internally, this should generate the exact same DQL as before, and I can prove it! Add a dd() with $query and, instead of saying ->getSQL(), say ->getDQL().

... lines 1 - 27
public function findAllOrdered(): array
{
... lines 30 - 32
$query = $qb->getQuery();
dd($query->getDQL());
... lines 35 - 36
}
... lines 38 - 82

When we try that... yeah! That is exactly what we wrote before! So, no surprise, if we remove that dd() and refresh... we're back to working! It's just that easy.

... lines 1 - 27
public function findAllOrdered(): array
{
... line 30
$qb = $this->createQueryBuilder('category')
->addOrderBy('category.name', Criteria::DESC);
$query = $qb->getQuery();
return $query->getResult();
}
... lines 37 - 81

Okay, we have the QueryBuilder basics down. Let's get more complex by adding andWhere() and orWhere() next.

Leave a comment!

0
Login or Register to join the conversation
Cat in space

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

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": ">=8.1",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "beberlei/doctrineextensions": "^1.3", // v1.3.0
        "doctrine/doctrine-bundle": "^2.7", // 2.9.1
        "doctrine/doctrine-migrations-bundle": "^3.2", // 3.2.2
        "doctrine/orm": "^2.13", // 2.15.1
        "symfony/asset": "6.2.*", // v6.2.7
        "symfony/console": "6.2.*", // v6.2.10
        "symfony/dotenv": "6.2.*", // v6.2.8
        "symfony/flex": "^2", // v2.2.5
        "symfony/framework-bundle": "6.2.*", // v6.2.10
        "symfony/proxy-manager-bridge": "6.2.*", // v6.2.7
        "symfony/runtime": "6.2.*", // v6.2.8
        "symfony/twig-bundle": "6.2.*", // v6.2.7
        "symfony/webpack-encore-bundle": "^1.16", // v1.16.1
        "symfony/yaml": "6.2.*" // v6.2.10
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.4", // 3.4.4
        "symfony/maker-bundle": "^1.47", // v1.48.0
        "symfony/stopwatch": "6.2.*", // v6.2.7
        "symfony/web-profiler-bundle": "6.2.*", // v6.2.10
        "zenstruck/foundry": "^1.22" // v1.32.0
    }
}
userVoice