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
}
}
Hi there,
In Symfony debug tool bar, I am able to get a RUNNABLE query such as below - This is very convenient for me to copy and paste it in any query console and run it manually for debugging purpose.
Runnable query from debug console:
SELECT count(DISTINCT e0_.id) AS sclr_0 FROM estate e0_ INNER JOIN status s1_ ON e0_.status_id = s1_.id INNER JOIN community c2_ ON e0_.community_id = c2_.id INNER JOIN address a3_ ON e0_.address_id = a3_.id INNER JOIN city c4_ ON a3_.city_id = c4_.id INNER JOIN province p5_ ON a3_.province_id = p5_.id INNER JOIN points p6_ ON e0_.id = p6_.estate_id WHERE s1_.current = 'active' AND c2_.name = 'erin woods';
My question is how can I get a RUNNABLE query using var_dump or dump and die dd() within source code? Because not all queries will be shown in Symfony debug tool bar. The best I can do right now is using method ->getDQL() to output the query string of which I have to modify some syntax before I can copy/paste and run it in any query console. An example of ->getDQL() is below, as you can see it is not ready to be executed as sql query.
DQL query from dd($returnQueryResultByCommunityQueryOnly->getDQL()):
SELECT e, s, co, a, c, pr, po FROM App\Entity\Estate e INNER JOIN e.statuses s INNER JOIN e.community co INNER JOIN e.address a INNER JOIN a.city c INNER JOIN a.province pr INNER JOIN e.points po WHERE s.current = 'active' AND co.name = :val ORDER BY po.rank ASC
When I tried with this dump
$returnQueryResultByCommunityQueryOnly = $this->addIsActiveQueryBuilder("active")
->innerJoin('e.statuses', 's')
->addSelect('s') // avoid repeated queries n+1 problem / bad performance
->innerJoin('e.address', 'a')
->addSelect('a')
->innerJoin('a.city', 'c')
->addSelect('c')
->innerJoin('e.community', 'com')
->addSelect('com')
->andWhere('com.name = :val')
->setParameter('val', $communityName)
->orderBy('e.marketDay', 'ASC')
->getQuery();
dd($returnQueryResultByCommunityQueryOnly);
I get this dql syntax not 'real' sql, I need to get SQL language:
Doctrine\ORM\Query {#9149
-_state: 2
-_parsedTypes: []
-_dql: "SELECT e, s, a, c, com FROM App\Entity\Estate e INNER JOIN e.statuses s INNER JOIN e.address a INNER JOIN a.city c INNER JOIN e.community com WHERE s.current = 'active' AND com.name = :val ORDER BY e.marketDay ASC"
-_parserResult: null
-_firstResult: null
-_maxResults: null
-_queryCache: null
-_expireQueryCache: false
-_queryCacheTTL: null
-_useQueryCache: true
#parameters: Doctrine\Common\Collections\ArrayCollection {#9148
-elements: array:1 [
0 => Doctrine\ORM\Query\Parameter {#9147
-name: "val"
-value: "brentwood"
-type: 2
}
]
}
#_resultSetMapping: null
#_em: Doctrine\ORM\EntityManager {#2268 …11}
#_hints: []
#_hydrationMode: 1
#_queryCacheProfile: null
#_expireResultCache: false
#_hydrationCacheProfile: null
#cacheable: false
#hasCache: false
#cacheRegion: null
#cacheMode: null
#cacheLogger: null
#lifetime: 0
}
Thank you!