Doctrine查询构建器使用内部连接与条件 [英] Doctrine query builder using inner join with conditions

问题描述

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

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

首先我试过

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

但是我收到以下错误

Error: expected end of string, got 'ON'

然后我试过

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

似乎正在工作。不过，有谁知道第一次尝试有什么问题？我想做第一个工作，因为它类似于SQL的结构如何。感谢提前！

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!

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

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

编辑：以下是整个代码

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

推荐答案

'要回答我自己的问题。

I'm going to answer my own question.

1. innerJoin应该使用关键字WITH而不是ON（Doctrine的文档[13.2。 6.帮助方法]不正确; [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');

或

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

这篇关于Doctrine查询构建器使用内部连接与条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！