Doctrine Query Builder不使用UPDATE和INNER JOIN

In my repository I have this query:

在我的存储库中，我有这个查询：

$qb = $this->getEntityManager()->createQueryBuilder();
$qb
    ->update('MyBundle:Entity1', 'e1') 
    ->join('e1.Entity2', 'e2')
    ->set('e1.visibile', '1')
    ->andWhere('e2.id = :id')->setParameter("id", 123)
;

throw this error

抛出这个错误

[Semantical Error] line 0, col 66 near 'e2.id = :id': Error: 'e2' is not defined

I have checked the relation and it is right. Is there any issue using join in query update?

我检查了这种关系，这是正确的。在查询更新中使用join是否有任何问题？

3 个解决方案

#1

You can not use join on update and delete queries. You have to use subqueries.

您无法在更新和删除查询时使用联接。你必须使用子查询。

Joins are not supported on update and delete queries because it is not supported on all dbms. It won't be implemented in Doctrine 1 or Doctrine 2. You can however get the same affect by using subqueries.

更新和删除查询不支持联接，因为并非所有dbms都支持联接。它不会在Doctrine 1或Doctrine 2中实现。但是，您可以通过使用子查询获得相同的效果。

http://www.doctrine-project.org/jira/browse/DC-646

If you are using MySQL, using subqueries will not work. You will have then to use 2 queries.

如果您使用的是MySQL，则使用子查询将无法正常工作。然后，您将使用2个查询。

In MySQL, you cannot modify a table and select from the same table in a subquery

在MySQL中，您无法修改表并从子查询中的同一个表中进行选择

http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

#2

Doctrine DQL does not support join in update.

Doctrine DQL不支持join in update。

Try doing the following :

尝试执行以下操作：

$qb = $this->getEntityManager()->createQueryBuilder();
$qb
    ->update('MyBundle:Entity1', 'e1') 
    ->set('e1.visibile', '1')
    ->where('e1.Entity2 = :id')
    ->setParameter("id", 123)
;

You can set the id, as long as it is the primary key, of the linked entity directly as if it was the entity, Doctrine will map it.

您可以直接设置链接实体的ID，只要它是主键，就像它是实体一样，Doctrine将映射它。

I'm doing the exact same thing in my queries and it works.

我在查询中做了完全相同的事情并且它有效。

#3

try using a subquery instead Join will not work in DQL while you re doing an update:

尝试使用子查询而不是在进行更新时，加入在DQL中不起作用：

LEFT JOIN, or JOINs in particular are only supported in UPDATE statements of MySQL. DQL abstracts a subset of common ansi sql, so this is not possible. Try with a subselect:

只有MySQL的UPDATE语句支持LEFT JOIN或JOIN。 DQL抽象了常见ansi sql的子集，因此这是不可能的。尝试使用subselect：

$qb = $this->getEntityManager()->createQueryBuilder();
    $qb ->update('MyBundle:Entity1', 'e') 
        ->set('e.visibile', '1')
        ->where('e.id IN (SELECT e1.id FROM Entity1 e1 INNER JOIN e2.Entity2 e2 WHERE e2 = :id')
        ->setParameter("id", 123);

#1