Doctrine query builder using inner join with conditions

Mr. 14 picture Mr. 14 · Mar 13, 2013 · Viewed 135.6k times · Source

I'd like to construct the following SQL using Doctrine's query builder:

select c.*
from customer c
join phone p
on p.customer_id = c.id
and p.phone = :phone
where c.username = :username

First I tried

$qb->select('c')
    ->innerJoin('c.phones', 'p', Join::ON, $qb->expr()->andx(
        $qb->expr()->eq('p.customerId', 'c.id'),
        $qb->expr()->eq('p.phone', ':phone')
    ))
    ->where('c.username = :username');

But I'm getting the following error

Error: expected end of string, got 'ON'

Then I tried

$qb->select('c')
    ->innerJoin('c.phones', 'p')
    ->where('c.username = :username')
    ->andWhere('p.phone = :phone');

which seems to be working. However, does anyone know what's wrong with the first attempt? I'd like to make the first one work since it resembles more closely to how SQL is structured. Thanks in advance!

Note: I know we can also write native mysql or dql with Doctrine, but I'd prefer query builder.

EDIT: Below is the entire code

namespace Cyan\CustomerBundle\Repository;

use Cyan\CustomerBundle\Entity\Customer;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr\Join;

class CustomerRepository extends EntityRepository
{
    public function findCustomerByPhone($username, $phone)
    {
        $qb = $this->createQueryBuilder('c');

        $qb->select('c')
            ->innerJoin('c.phones', 'p', Join::ON, $qb->expr()->andx(
                $qb->expr()->eq('p.customerId', 'c.id'),
                $qb->expr()->eq('p.phone', ':phone')
            ))
            ->where('c.username = :username');

//        $qb->select('c')
//            ->innerJoin('c.phones', 'p')
//            ->where('c.username = :username')
//            ->andWhere('p.phone = :phone');

        $qb->setParameters(array(
            'username' => $username,
            'phone' => $phone->getPhone(),
        ));

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

Answer

Mr. 14 picture Mr. 14 · Mar 13, 2013

I'm going to answer my own question.

  1. innerJoin should use the keyword "WITH" instead of "ON" (Doctrine's documentation [13.2.6. Helper methods] is inaccurate; [13.2.5. The Expr class] is correct)
  2. no need to link foreign keys in join condition as they're already specified in the entity mapping.

Therefore, the following works for me

$qb->select('c')
    ->innerJoin('c.phones', 'p', 'WITH', 'p.phone = :phone')
    ->where('c.username = :username');

or

$qb->select('c')
    ->innerJoin('c.phones', 'p', Join::WITH, $qb->expr()->eq('p.phone', ':phone'))
    ->where('c.username = :username');