Buy

The QueryBuilder

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:

25 lines src/AppBundle/Entity/CategoryRepository.php
... 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:

25 lines src/AppBundle/Entity/CategoryRepository.php
... 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():

25 lines src/AppBundle/Entity/CategoryRepository.php
... 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:

25 lines src/AppBundle/Entity/CategoryRepository.php
... 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:

24 lines src/AppBundle/Entity/CategoryRepository.php
... 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!

  • 2016-11-14 Hakim Ch

    super! thank you very much :D

  • 2016-11-14 Victor Bocharsky

    Yes! Then `gory` will be an alias of that table, in which entity repository you call this "createQueryBuilder('gory')" method, i.e. if you call "$this->createQueryBuilder('gory')" inside the CategoryRepository, then `gory` will be an alias of category table ;)

    Cheers!

  • 2016-11-14 Hakim Ch

    it make sens... so correct me if im wrong, so we can call is $this->createQueryBuilder('gory') if we want, and work with it ?

  • 2016-11-14 Victor Bocharsky

    Ah I see.. So this alias is arbitrary and it's set for the first time when you call "createQueryBuilder()" if you use Doctrine query builder, e.g. "$this->createQueryBuilder('cat')", where `cat` is the alias of table for Category entity (because you're calling this method from CategoryRepository). If you use DQL, you'll need to specify alias right after the table name like "SELECT cat FROM AppBundle\Entity\Category AS cat". Does it answer your question?

    Cheers!

  • 2016-11-14 Hakim Ch

    Yes i khnow, but my question is about when or where in the project we setup the alias of catecory :(

  • 2016-11-14 Victor Bocharsky

    Hey Hakim,

    Actually, it's a core SQL feature, you can use `AS` keyword to make alias for table or field, but also you can omit it. Check the wiki page about SQL aliases: https://en.wikipedia.org/wiki/...

    Cheers!

  • 2016-11-13 Hakim Ch

    In the start of the video, you were creating a queryBuilder with the "cat" alias, how it's possible to make alias of tables ?

  • 2016-05-18 BondashMaster

    Thanks. I got it. If somebody has the same problem the things is you need to type the "Single Quotation mark" or "apostrophe" around the alias to acces the fields. If you dont PHPstorm assumes you are trying to acces a variable instead of the repository entity.

  • 2016-05-18 weaverryan

    Hi there!

    I'm not actually sure *which* plugin provides this, but I would strongly assume that it's coming from either the Symfony plugin or PHP Annotations plugin - I don't really have anything else installed. I just double-checked and I *do* still get this type of auto-completion on the latest version of PhpStorm and both plugins (I even tried it in a totally different project / entity).

    If you figure out what the problem is, let me know! Sometimes, for subtle reasons, the plugin won't *always* work perfectly.

    Cheers!

  • 2016-05-16 BondashMaster

    in minute 0.44 when you use the cat alias you get the list of the
    fields. I'm not getting those.:/ Any additional plugin or something
    else that I have to install??? I'm using PHPStorm + Symfony and
    Annotation plugin. I get everything else of autocomplete except of
    those.