Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine
TRACK

Symfony 5 >

COURSE

Go Pro with Doctrine Queries

Buy Access to Course

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
Previous Chapter

Next Chapter
Edit on GitHub

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!

Most Useful Newest Oldest
1
Login or Register to join the conversation
Dung L. Avatar
Dung L. Avatar Dung L. | posted 3 years ago | edited

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:

<br />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';<br />

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()):

<br />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<br />

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!

Reply
Cat in space

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

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 :).
Symfony 2

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
    }
}
Edit on GitHub
Previous Chapter
Next Chapter