This tutorial has a new version, check it out!

Doctrine’s 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 $12.00

Doctrine’s QueryBuilder

What if we wanted to find a User by matching on the email or username columns? We would of course add a findOneByUsernameOrEmail method to UserRepository:

// src/Yoda/UserBundle/Entity/UserRepository.php
// ...

class UserRepository extends EntityRepository
    public function findOneByUsernameOrEmail()
        // ... todo - get your query on

To make queries, you can use an SQL-like syntax called DQL, for Doctrine query language. You can even use native SQL queries if you’re doing something really complex.

But most of the time, I recommend using the awesome query builder object. To get one, call createQueryBuilder and pass it an “alias”. Now, add the where clause with our OR logic:

// src/Yoda/UserBundle/Entity/UserRepository.php
// ...

public function findOneByUsernameOrEmail($username)
    return $this->createQueryBuilder('u')
        ->andWhere('u.username = :username OR = :email')
        ->setParameter('username', $username)
        ->setParameter('email', $username)

The query builder has every method you’d expect, like leftJoin, orderBy and groupBy. It’s really handy.

The stuff inside andWhere looks similar to SQL except that we use “placeholders” for the two variables. Fill each of these in by calling setParameter. The reason this is separated into two steps is to avoid SQL injection attacks, which are really no fun.

To finish the query, call getQuery and then getOneOrNullResult, which, as the name sounds, will return the User object if it’s found or null if it’s not found.


To learn more about the Query Builder, see The QueryBuilder.

To try this out, let’s temporarily reuse the EventController’s indexAction. Get the UserRepository by calling getRepository on the entity manager. Remember, the argument you pass to getRepository is the entity’s “shortcut name”: the bundle name followed by the entity name:

// src/Yoda/EventBundle/Controller/EventController.php
// ...

public function indexAction()
    $em = $this->getDoctrine()->getManager();

    // temporarily abuse this controller to see if this all works
    $userRepo = $em->getRepository('UserBundle:User');

    // ...

Now that we have the UserRepository, let’s try our new method and dump the result:

public function indexAction()
    // ...
    $userRepo = $em->getRepository('UserBundle:User');

    // ...

When we refresh, we see the user. If we try the email instead, we get the same result:


Cool! Now let’s get rid of these debug lines - I’m trying to get a working project going here people!

But this is a really common pattern we’ll see more of: use the repository in a controller to fetch objects from the database. If you need a special query, just add a new method to your repository and use it.

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:


  • 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 AS sclr_0 FROM estate e0_ INNER JOIN status s1_ ON e0_.status_id = INNER JOIN community c2_ ON e0_.community_id = INNER JOIN address a3_ ON e0_.address_id = INNER JOIN city c4_ ON a3_.city_id = INNER JOIN province p5_ ON a3_.province_id = INNER JOIN points p6_ ON = p6_.estate_id WHERE s1_.current = 'active' AND = '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 co INNER JOIN e.address a INNER JOIN c INNER JOIN a.province pr INNER JOIN e.points po WHERE s.current = 'active' AND = :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')
    ->innerJoin('', 'c')
    ->innerJoin('', 'com')
    ->andWhere(' = :val')
    ->setParameter('val', $communityName)
    ->orderBy('e.marketDay', 'ASC')


    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 c INNER JOIN com WHERE s.current = 'active' AND = :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


  • 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(' = :name AND (e.role = :role OR e.role = :anotherRole) AND = 1')
    ->setParameter('name', $name)
    ->setParameter('role', $role)
    ->setParameter('anotherRole', $anotherRole)

    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


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


  • 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 ;)


  • 2018-02-22 Junaid Farooq


    For me the following clause worked as is there in the symfony docs:

    public function findOneByUsernameOrEmail($username)
    ->where('u.username = :username OR = :email')
    instead of:
    ->andWhere('u.username = :username OR = :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: 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!


  • 2016-12-21 Shairyar Baig


    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?


  • 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();
    ->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 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, AS name_5, p0_.street AS street_6, 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: 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 ;)


  • 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?


  • 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:


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


  • 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