使用具有条件的内部联接的Doctrine查询构建器

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

我想使用Doctrine的查询构建器构造以下SQL：

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!

这似乎工作。但是，有人知道第一次尝试有什么问题吗？我想让第一个工作，因为它更接近SQL的结构。提前致谢！

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

注意：我知道我们也可以使用Doctrine编写本机mysql或dql，但我更喜欢查询构建器。

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();
    }
}

2 个解决方案

#1

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)
innerJoin应该使用关键字“WITH”而不是“ON”（Doctrine的文档[13.2.6。帮助方法]是不准确的; [13.2.5.Expr类]是正确的）
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');

要么

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

#2

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...

否则，将不会检测到Join类，并且您的脚本将崩溃...

Here the constructor of the innerJoin method:

这里是innerJoin方法的构造函数：

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

你可以在这里找到其他的可能性（不只是加入“ON”，还有“WITH”等...）：http：//docs.doctrine-project.org/en/2.0.x/reference/query-builder。 HTML＃的-EXPR级

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.

否则我认为你正在执行ON和WITH的混合，也许是问题。

#1