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->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. 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\CustomerB…

Accepted 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->select('c')
    ->innerJoin('c.phones', 'p', 'WITH', 'p.phone = :phone')
    ->where('c.username = :username')
    ->setParameter('phone', $phone)
    ->setParameter('username', $username);
```

or

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

Doctrine query builder using inner join with conditions

Solution

Problem