学说QueryBuilder需要在左Join条件下进行更改

Question

我有本机SQL查询，左JOIN在有或条件时，如何在查询构建器中表示它？

 $query = "  SELECT  te.id
            FROM    task_executions AS te
            INNER JOIN tasks AS t ON t.id = te.task_id
            LEFT JOIN cost_objects AS co ON co.id = t.cost_object_id
            LEFT JOIN cost_object_managers AS com ON com.cost_object_id = co.id OR com.cost_object_id = co.parent_id

我需要在查询构建器中代表它。但是在User实体中，我有许多tomany关系，没有单独的表，当我尝试左JON加入WITH条件时，这不是我需要的。我需要ON

的更改关系

LEFT JOIN cost_object_managers AS com ON com.cost_object_id = co.id OR com.cost_object_id = co.parent_id

用户实体

class User
{
...
/**
 * @ORMManyToMany(targetEntity="CostObject", mappedBy="users")
 */
private $costObjects;
}

CostObject实体

class CostObject
{
    /**
 * @var CostObject
 *
 * @ORMManyToOne(targetEntity="CostObject", inversedBy="children")
 * @ORMJoinColumns({
 *   @ORMJoinColumn(name="parent_id", referencedColumnName="id", onDelete="CASCADE")
 * })
 */
private $parent;
    /**
 * @var ArrayCollection
 *
 * @ORMManyToMany(targetEntity="User", inversedBy="costObjects")
 * @ORMJoinTable(name="cost_object_managers",
 *      joinColumns={@ORMJoinColumn(name="cost_object_id", referencedColumnName="id", onDelete="CASCADE")},
 *      inverseJoinColumns={@ORMJoinColumn(name="user_id", referencedColumnName="id", onDelete="CASCADE")}
 * )
 */
private $users;

和我的查询构建器没有条件

        $qb->select('te')
        ->from('AppBundle:TaskExecution', 'te')
        ->innerJoin('te.task', 't')
        ->leftJoin('t.costObject', 'co')
        ->leftJoin('co.users', 'com')

这是$query->getSQL()

SELECT some_name FROM task_executions t0_ INNER JOIN tasks t1_ ON t0_.task_id = t1_.id LEFT JOIN cost_objects c2_ ON t1_.cost_object_id = c2_.id LEFT JOIN cost_object_managers c4_ ON c2_.id = c4_.cost_object_id LEFT JOIN users u3_ ON u3_.id = c4_.user_id ORDER BY t0_.execution_start DESC

在此示例中，我看到ON关系条件LEFT JOIN users u3_ ON u3_.id = c4_.user_id。并且需要像本机SQL

中更改它

现在我有

        $qb->select('te')
        ->from('AppBundle:TaskExecution', 'te')
        ->innerJoin('te.task', 't')
        ->leftJoin('t.costObject', 'co')
        ->leftJoin(
            'co.users',
            'com',
            Join::ON,
            $qb->expr()->orX(
                'co = com.costObjects',
                'co.parent = com.costObjects'
            )
        )

但是出错了

[Syntax Error] line 0, col 112: Error: Expected end of string, got 'ON'

如果我使用了WITH条件，在我的SQL表示我仍然有ID的关系，我不需要

            ->leftJoin(
            'co.users',
            'com',
            Join::WITH,
            $qb->expr()->orX(
                'co MEMBER OF com.costObjects',
                'co.parent MEMBER OF com.costObjects'
            )
        )
LEFT JOIN users u3_ ON u3_.id = c4_.user_id AND (EXISTS (SELECT 1 FROM cost_object_managers c5_ INNER JOIN cost_objects c6_ ON c5_.cost_object_id = c6_.id WHERE c5_.user_id = u3_.id AND c6_.id IN (c2_.id)) OR EXISTS (SELECT 1 FROM cost_object_managers c5_ INNER JOIN cost_objects c6_ ON c5_.cost_object_id = c6_.id WHERE c5_.user_id = u3_.id AND c6_.id IN (c2_.parent_id)))

我的意思是 users u3_ ON u3_.id = c4_.user_id AND，但是在本机查询中，我们只有 LEFT JOIN cost_object_managers AS com ON com.cost_object_id = co.id OR com.cost_object_id = co.parent_id

使用ON条件类型在查询构建器中如何复制它？

Answer 1

如果您有查询并且对您有用，则无需完成所有工作即可将其转换为dql或QueryBuilder programmatic语法。您可以只使用学说的本机查询，然后 - 如果需要 - 将结果映射到您的对象。只需创建一个自定义存储库，然后在其中一种大致看起来像这样的新方法：

public function findTaskExecutionBy...()
{
    $query = $this->entityManager->createNativeQuery('SELECT te.id FROM ...');
    return $query->getSingleScalarResult(); // If it's just one id you expect
}

如果您希望返回多个ID，也可以使用$query->getResult()。或使用ResultsetMapping（如果需要整个任务对象：

）

$rsm = new ResultSetMappingBuilder($this->entityManager);
$rsm->addRootEntityFromClassMetadata('AppEntityTask', 'te');
$query = $this->entityManager->createNativeQuery(
    'SELECT te.* FROM ...',

您还可以检查学说文档以获取更详细的说明和更多示例：http：//docs.doctrine-project.org/projects/doctrine-orm/en/latest/latest/reference/native-sql.html.html $ rsm ）;