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