Using Doctrine, I am trying to delete records in a single table based on data gathered from multiple tables.
'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.
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'
What is the equivalent in Doctrine? I have been struggling and experimenting for an hour or so now.
At the moment I have this:
$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 ) );
(My Doctrine models are 'Company_group' for the 'companies_groups' table and 'Group' for the 'groups' table)
Which produces this SQL:
DELETE FROM companies_groups, groups WHERE (companyid = ? AND grouptype = 'public' AND id = groupid)
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.