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!

2020-02-08 dung le

Thank you Diego Aguiar for the SOF link! It helps.
2020-02-07 Diego Aguiar

Hey dung le
As far as I know there is no way to get the final SQL from Doctrine because Doctrine sends the "prepared" SQL to the Database but what you can do is to enable logging in your Database so you can get the final SQL. Here you can see how to enable it: https://stackoverflow.com/a...
Cheers!
2020-02-06 dung le

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!
2018-02-23 Junaid Farooq

@Diego Aguiar
Thanks
2018-02-23 Diego Aguiar

Yes! just like that :)
2018-02-23 Junaid Farooq

Hey Diego Aguiar
Thanks for replying. You mean in the context of Doctrine this would be like:
`
return $this->createQueryBuilder('e')
->where('e.name = :name AND (e.role = :role OR e.role = :anotherRole) AND e.active = 1')
->setParameter('name', $name)
->setParameter('role', $role)
->setParameter('anotherRole', $anotherRole)
->getQuery()
->execute();
`
Please clarify.
2018-02-22 Diego Aguiar
Hey Junaid Farooq
If you specify a where clause and then another one, as Victor said, the first one will be overridden, so you have to use "andWhere" to concatenate your where clauses, but if need both clauses AND & OR, it's better to write them all in a single "where", so you don't get problems of order evaluation, like this query:
```
SELECT * FROM table 
WHERE name = 'diego AND (role = 'admin' OR role = 'super_admin') AND active = 1
```
Cheers!
2018-02-22 Junaid Farooq

Hey Victor Bocharsky
Ah! My bad. I had forgotten @return User|null in the PHPDoc due to which i had to wrap it in a try catch block but now that i put it there, i dont need a try catch block anymore. I can also use andWhere() now. Great. Think the error was somehow related to missing try-catch block or PHPDoc Comment or something else. Anyways It is working now. I just need clarification on one point though. You say that if i only use a bunch of where clauses, the previous ones are gonna be overridden, can i use where and andWhere together or multiple andWhere clauses if i have multiple conditions and where would i need a simple where clause. Thanks a lot.
Cheers!
2018-02-22 Victor Bocharsky

Hey Junaid,
Actually, where() and andWhere() are mostly same but with a subtle difference as you can guess from their names: where() will always override all the where block of your query so if you will have some logic in WHERE statement of your query and unintentionally call where() - you'll lost all our previous WHERE statements. That's why Ryan uses andWhere() to avoid this dumb misprints. It's even more useful later when you'll modify the query to add more complex logic to it and just when you'll do some refactoring. And that's it :) Doctrine is smart enough to convert andWhere() into simple where() if it contains the first WHERE statement
P.s. about wrapping your query with try-catch - only makes sense if you expects some exceptions which should be ok in some cases, otherwise you don't need it - you'll probably want to see a big error to know that's something is not good with your query ;)
Cheers!
2018-02-22 Junaid Farooq

Hello,
For me the following clause worked as is there in the symfony docs:
public function findOneByUsernameOrEmail($username)
..........
->where('u.username = :username OR u.email = :email')
...........
instead of:
..........
->andWhere('u.username = :username OR u.email = :email')
.......
as you are using here. The reason for this maybe, it is expecting another clause before it if i put `andWhere()` and hence it doesnt work as there isnt any other clause. Besides I had to wrap the query builder in a try catch block which you clearly dont do. Can you please the reason behind this. Thanks in advance.
2016-12-22 Shairyar Baig

Ryan, I cannot thank you enough. This did help me fix the problem.
2016-12-21 weaverryan

Hey Shairyar!
I'm not completely sure, but I believe the issue is that Postgres (being a pretty smart database) has a special JSON field type - see details here: http://stackoverflow.com/qu.... So, with MySQL, this is just saved in a string field where the contents are JSON (and the LIKE query works). But Postgresql I believe puts it into an actual, native JSON field. This is actually good, as I believe they more natively support searching on the contents of that field, but I'm not exactly sure how that query would look. The StackOverflow has a few links that might contain some things to try out.
Let us know if this helps point you in the right direction!
Cheers!
2016-12-21 Shairyar Baig

Hi,
The field type is json_array and the role in database is saved as ["ROLE_USER","ROLE_EMPLOYER"]
2016-12-21 Victor Bocharsky

Hey Shairyar,
What type of `u.roles` column is exactly in PostgreSQL? Could you double check it's really a string field?
Cheers!

2016-12-21 Shairyar Baig

I am working on a project where the database being used is PostgreSQL, while the following query seems to work perfectly file on MySQL but I am struggling to get it to work on PostgreSQL.


namespace AppBundle\Repository;
use \Doctrine\ORM\EntityRepository;
class UserRepository extends EntityRepository
{
    public function findByRole($role)
    {
        $qb = $this->_em->createQueryBuilder();
        $qb->select('u')
            ->from($this->_entityName, 'u')
            ->where('u.roles LIKE :roles')
            ->setParameter('roles', '%"'.$role.'"%');

        return $qb->getQuery()->getResult();
    }
}

The error I get is as following

An exception occurred while executing 'SELECT p0_.id AS id_0, p0_.username AS username_1, p0_.password AS password_2, p0_.is_active AS is_active_3, p0_.roles AS roles_4, p0_.name AS name_5, p0_.street AS street_6, p0_.city AS city_7, p0_.state AS state_8, p0_.zip_code AS zip_code_9, p0_.phone_number AS phone_number_10, p0_.dob AS dob_11, p0_.company_name AS company_name_12, p0_.company_slug AS company_slug_13, p0_.company_logo AS company_logo_14, p0_.company_details AS company_details_15, p0_.stripe_customer_id AS stripe_customer_id_16, p0_.created_at AS created_at_17, p0_.updated_at AS updated_at_18 FROM px_user p0_ WHERE p0_.roles LIKE ?' with params ["%\"ROLE_EMPLOYER\"%"]:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json ~~ unknown LINE 1: ...at AS updated_at_18 FROM px_user p0_ WHERE p0_.roles LIKE $1 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I will really appreciate if I can get some understanding on why this is failing?

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/wi...
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.

TRACK

Symfony 5 >

COURSE

Go Pro with Doctrine Queries

The QueryBuilder

Keep on Learning!

Creating the Query Builder

Building the Query

Leave a comment!

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

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