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();
}
}
I'm going to answer my own question.
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');