Buy

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 u.email = :email')
        ->setParameter('username', $username)
        ->setParameter('email', $username)
        ->getQuery()
        ->getOneOrNullResult()
    ;
}

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.

Note

To learn more about the Query Builder, see doctrine-project.org: 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');
    var_dump($userRepo->findOneByUsernameOrEmail('user'));die;

    // ...
}

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

var_dump($userRepo->findOneByUsernameOrEmail('user@user.com'));die;

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!

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

    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.