SQL 使用带条件的内部连接的 Doctrine 查询构建器

Doctrine query builder using inner join with conditions

提问by Mr. 14

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

回答by Mr. 14

I'm going to answer my own question.

我要回答我自己的问题。

1. 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)
2. no need to link foreign keys in join condition as they're already specified in the entity mapping.

1. innerJoin 应该使用关键字“WITH”而不是“ON”（Doctrine 的文档 [13.2.6. Helper 方法] 不准确；[13.2.5. Expr 类] 是正确的）
2. 无需在连接条件中链接外键，因为它们已在实体映射中指定。

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');

回答by Sybio

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:

但是你需要使用来自Doctor的类Join的命名空间：

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 classwon'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#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.

否则，我认为您正在执行 ON 和 WITH 的混合，这可能是问题所在。