使用Doctrine在MySQL数据库上进行多表删除的语法是什么？ [英] What is the syntax for a multi-table delete on a MySQL database using Doctrine?

问题描述

使用教义，我试图删除单个表中的记录基于从多个表收集的数据。

Using Doctrine, I am trying to delete records in a single table based on data gathered from multiple tables.

'company_groups'是将公司与组关联的关联表。我想删除与特定公司相关联的此表中的所有记录，但限制只有与公开组相关联的company_groups记录将被删除。

'companies_groups' is an association table linking 'companies' to 'groups'. I want to delete all records in this table linked to a specific company, with the restriction that only 'companies_groups' records linked to a 'public' group will be deleted.

如果我要用纯SQL写这个，那么看起来就像这样：

If I were to write this in pure SQL, it would look something like this:

DELETE companies_groups
FROM companies_groups, groups
WHERE companies_groups.companyID = 7
AND companies_groups.groupID = groups.id
AND groups.groupType = 'public'

在Doctrine中的等价物是什么？我一直在努力和试验一个小时左右。

What is the equivalent in Doctrine? I have been struggling and experimenting for an hour or so now.

目前我有这样的一个：

$query = Doctrine_Query::create()
  ->delete('Company_group cg')
  ->from('Company_group cg, Group g')
  ->where( "cg.companyID = ? AND g.groupType = 'public' AND g.id = cg.groupID ", array( $companyID ) );

（我的教义模型是'company_groups''''group_group'''''组的表）

(My Doctrine models are 'Company_group' for the 'companies_groups' table and 'Group' for the 'groups' table)

哪个生成此SQL：

DELETE FROM companies_groups, groups WHERE (companyid = ? AND grouptype = 'public' AND id = groupid)

看到生成的SQL在DELETE和FROM之间缺少companies_groups，并且限定符被删除（意思是id将是不明确的）。

You can see that the generated SQL is missing 'companies_groups' between DELETE and FROM, and that the qualifiers are dropped (meaning 'id' will be ambiguous).

让我知道我可以提供任何额外的信息，这将是有帮助的。

Let me know if there is any additional info I can provide that would be helpful.

推荐答案

解决它。而不是尝试自己构建联接，而是使用一个子查询，并没有试图强制Doctrine以特定的方式构造SQL。

Solved it. Rather than try to build the join myself I used a subquery and didn't try to force Doctrine to construct the SQL in a specific way.

对于任何需要的人做这样的事情，这里是最终的工作：

For anyone else that ever needs to do something like this, here is what ended up working:

$query = Doctrine_Query::create()
  ->delete('Company_group')
  ->where( "companyID = ?", array( $companyID ) )
  ->addWhere( "groupID IN (SELECT g.id FROM Group g WHERE g.groupType = 'public')" );

干杯。

这篇关于使用Doctrine在MySQL数据库上进行多表删除的语法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！