使用查询构建器删除Doctrine 2

I have two Entities with relation OneToMany, Project and Services. Now i want to remove all the services by project_id.

我有两个实体关系OneToMany，项目和服务。现在我想通过project_id删除所有服务。

First attempt:

第一次尝试：

$qb = $em->createQueryBuilder();
$qb->delete('Services','s');
$qb->andWhere($qb->expr()->eq('s.project_id', ':id'));
$qb->setParameter(':id',$project->getId());

This attempt fails with the Exception Entity Service does not have property project_id. And it's true, that property does not exists, it's only in database table as foreign key.

此尝试失败，异常实体服务没有属性project_id。确实，该属性不存在，它只在数据库表中作为外键。

Second attempt:

第二次尝试：

$qb = $em->createQueryBuilder();
$qb->delete('Services','s')->innerJoin('s.project','p');
$qb->andWhere($qb->expr()->eq('p.id', ':id'));
$qb->setParameter(':id',$project->getId());

This one generetate a non valid DQL query too.

这个也是一个无效的DQL查询。

Any ideas and examples will be welcome.

任何想法和例子都将受到欢迎。

2 个解决方案

#1

You're working with DQL, not SQL, so don't reference the IDs in your condition, reference the object instead.

您正在使用DQL而不是SQL，因此请不要在您的条件中引用ID，而是引用该对象。

So your first example would be altered to:

所以你的第一个例子将改为：

$qb = $em->createQueryBuilder();
$qb->delete('Services', 's');
$qb->where('s.project = :project');
$qb->setParameter('project', $project);

#2

If you really can't get project object and you have to handle only with id you can use this.

如果你真的无法获得项目对象而你必须只使用id来处理，你可以使用它。

Citation from doctrine documentation: There are two possibilities for bulk deletes with Doctrine. You can either issue a single DQL DELETE query or you can iterate over results removing them one at a time. (Below I paste only first solution)

来自学说文档的引用：使用Doctrine进行批量删除有两种可能性。您可以发出单个DQL DELETE查询，也可以迭代结果，一次删除一个。（下面我只粘贴第一个解决方案）

DQL Query The by far most efficient way for bulk deletes is to use a DQL DELETE query.

DQL查询最有效的批量删除方法是使用DQL DELETE查询。

Example that worked in my project

在我的项目中工作的示例

$q = $em->createQuery('delete from AcmeMyTestBundle:TemplateBlock tb where tb.template = '.intval($templateId));
$numDeleted = $q->execute();

In entity TemplateBlock I have property called template which is mapped to template_id in db.

在实体TemplateBlock中，我有一个名为template的属性，它被映射到db中的template_id。

But I agree that highly preferred way of doing it is using objects.

但我同意这样做的首选方法是使用对象。

#1