SymfonyCasts stands united with the people of Ukraine
If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.
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.
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 | |
| } | |
| } |
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 DESCThat'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.
// 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
}
}