The QueryBuilder

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

Doctrine speaks DQL, even though it converts it eventually to SQL. But actually, I don't write a lot of DQL. Instead, I use the QueryBuilder: an object that helps you build a DQL string. The QueryBuilder is one of my favorite parts of Doctrine.

Creating the Query Builder

Let's comment out the $dql stuff. To create a QueryBuilder, create a $qb variable and call $this->createQueryBuilder() from inside a repository. Pass cat as the argument - this will be the alias to Category:

... lines 1 - 12
class CategoryRepository extends EntityRepository
{
public function findAllOrdered()
{
$qb = $this->createQueryBuilder('cat')
... lines 18 - 22
}
}

Building the Query

Now, let's chain some awesomeness! The QueryBuilder has methods on it like andWhere, leftJoin and addOrderBy. Let's use that - pass cat.name as the first argument and DESC as the second:

... lines 1 - 12
class CategoryRepository extends EntityRepository
{
public function findAllOrdered()
{
$qb = $this->createQueryBuilder('cat')
->addOrderBy('cat.name', 'ASC');
... lines 19 - 22
}
... lines 24 - 25

This builds the exact same DQL query we had before. Because we're inside of the CategoryRepository, the createQueryBuilder() function automatically configures itself to select from the Category entity, using cat as the alias.

To get a Query object from this, say $qb->getQuery():

... lines 1 - 12
class CategoryRepository extends EntityRepository
{
public function findAllOrdered()
{
$qb = $this->createQueryBuilder('cat')
->addOrderBy('cat.name', 'ASC');
$query = $qb->getQuery();
... lines 20 - 22
}
}

Wow.

Remember how we printed the SQL of a query? We can also print the DQL. So let's see how our hard work translates into DQL:

... lines 1 - 14
public function findAllOrdered()
{
$qb = $this->createQueryBuilder('cat')
->addOrderBy('cat.name', 'ASC');
$query = $qb->getQuery();
var_dump($query->getDQL());die;
return $query->execute();
}
... lines 24 - 25

Refresh! Look closely:

SELECT cat FROM AppBundle\Entity\Category cat ORDER BY cat.name DESC

That's character-by-character the exact same DQL that we wrote before. So the query builder is just a nice way to help write DQL, and I prefer it because I get method auto-completion and it can help you re-use pieces of a query, like a complex JOIN, across multiple queries. I'll show you that later.

Remove the die statement and refresh to make sure it's working:

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

It looks perfect. To know more about the QueryBuilder, you can either keep watching (that's recommended) or use your IDE to see all the different methods the class has. But you should just keep watching.

Leave a comment!

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