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();
}
}
This question is related to
sql
symfony
doctrine-orm
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');
You can explicitly have a join like this:
$qb->innerJoin('c.phones', 'p', Join::ON, 'c.id = p.customerId');
But you need to use the namespace of the class Join from doctrine:
use Doctrine\ORM\Query\Expr\Join;
Or if you prefere like that:
$qb->innerJoin('c.phones', 'p', Doctrine\ORM\Query\Expr\Join::ON, 'c.id = p.customerId');
Otherwise, Join class won't be detected and your script will crash...
Here the constructor of the innerJoin method:
public function innerJoin($join, $alias, $conditionType = null, $condition = null);
You can find other possibilities (not just join "ON", but also "WITH", etc...) here: http://docs.doctrine-project.org/en/2.0.x/reference/query-builder.html#the-expr-class
EDIT
Think it should be:
$qb->select('c')
->innerJoin('c.phones', 'p', Join::ON, 'c.id = p.customerId')
->where('c.username = :username')
->andWhere('p.phone = :phone');
$qb->setParameters(array(
'username' => $username,
'phone' => $phone->getPhone(),
));
Otherwise I think you are performing a mix of ON and WITH, perhaps the problem.
Source: Stackoverflow.com