How to use with Doctrine the OR function when having multiple fields?

Question

I'm making an inlog form where the user can decide to use this username or email address in combination with its password. So normally it would something like: ((username OR email) AND password) in SQL. Used the following controller code only don't know how to add the OR function.

        $user = $this->getDoctrine()
            ->getRepository('Bundle:CusUser')
            ->findOneBy(
                array('username' => $user->getUsername(), 'email' => $user->getUsername(), 'password' => $user->getPassword())
            );

For both is getUsername used as this is the flied in the form.

Possible duplicate of [doctrine2 findby two columns OR condition](http://stackoverflow.com/questions/15229609/doctrine2-findby-two-columns-or-condition) — A.L, Feb 24 '16 at 20:29

chalasr · Accepted Answer · 2016-02-23T20:53:05.280

Actually there is no ->orBy(...) method in the default doctrine EntityRepository.
You can find all methods available in the API of this class.

Instead, use a QueryBuilder:

$query = $this->getDoctrine()
    ->getRepository('Bundle:CusUser')
    ->createQueryBuilder('u')
    ->select('u')
    ->where('u.username = :username')
    ->orWhere('u.email = :email')
    ->setParameter('username', $user->getUsername())
    ->setParameter('email', $user->getEmail())
    ->getQuery();

// Get the first result
$user = $query->setMaxResults(1)->getSingleResult();

Put it in a custom EntityRepository and use it with an array of arguments that you use as where and orWhere statement's parameters. e.g. :

public function findByOrBy(array $parameters)
{
    $query = $this->getEntityManager()->createQueryBuilder();
    // ...

    foreach ($parameters as $property => $value) {
        $query
            ->orWhere($property.' = :'.$property)
            ->setParameter($property, $value)
        // ...
    }
}

You can find the list of the available methods (almost all correspondences of native SQL methods) in the QueryBuilder API.

OP should also have a look at the [Expr class](http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/query-builder.html#the-expr-class). Gives much more flexibility when combining ANDs and ORs. — hasumedic, Feb 24 '16 at 09:29

score 0 · Answer 2 · answered Feb 23 '16 at 23:46

FOSUserBundle has a similar function to allow user to log in using email or username.

They simply check if the user input is an email or not :

From friendsofsymfony/user-bundle/Model/UserManager.php on line 102

/**
 * Finds a user either by email, or username
 *
 * @param string $usernameOrEmail
 *
 * @return UserInterface
 */
public function findUserByUsernameOrEmail($usernameOrEmail)
{
    if (filter_var($usernameOrEmail, FILTER_VALIDATE_EMAIL)) {
        return $this->findUserByEmail($usernameOrEmail);
    }

    return $this->findUserByUsername($usernameOrEmail);
}

So in you controller you could have this:

public function loginAction()
{
    //...

    if (filter_var($usernameOrEmail, FILTER_VALIDATE_EMAIL)) {
        $user = $this->get('doctrine')
            ->getRepository('Bundle:CusUser')
            ->findOneBy(array('email' => $usernameOrEmail, 'password' => $password));
    }
    else {
        $user = $this->get('doctrine')
            ->getRepository('Bundle:CusUser')
            ->findOneBy(array('username' => $usernameOrEmail, 'password' => $password));
    }

    //...

}

Hope this helps

FOSUserBundle use two different queries, how is it related to the second code block? — A.L, Feb 24 '16 at 20:24

How to use with Doctrine the OR function when having multiple fields?

2 Answers2