Doctrine query builder using inner join with conditions

Question

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- gt select  c       - gt innerJoin  c phones    p   Join  ON   qb- gt expr  - gt andx           qb- gt expr  - gt eq  p customerId    c id             qb- gt expr  - gt eq  p phone     phone              - gt where  c username    username      But I m getting the following error  Error  expected end of string  got  ON    Then I tried   qb- gt select  c       - gt innerJoin  c phones    p       - gt where  c username    username       - gt 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- gt createQueryBuilder  c              qb- gt select  c               - gt innerJoin  c phones    p   Join  ON   qb- gt expr  - gt andx                   qb- gt expr  - gt eq  p customerId    c id                     qb- gt expr  - gt eq  p phone     phone                              - gt where  c username    username                qb- gt select  c                 - gt innerJoin  c phones    p                 - gt where  c username    username                 - gt andWhere  p phone    phone              qb- gt setParameters array               username    gt   username               phone    gt   phone- gt getPhone                          query    qb- gt getQuery            return  query- gt getResult

User · Answer

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.

User · Answer

I m going to answer my own question    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  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- gt select  c       - gt innerJoin  c phones    p    WITH    p phone    phone       - gt where  c username    username      or   qb- gt select  c       - gt innerJoin  c phones    p   Join  WITH   qb- gt expr  - gt eq  p phone     phone        - gt where  c username    username

[sql] Doctrine query builder using inner join with conditions

Examples related to sql

Examples related to symfony

Examples related to doctrine-orm