[DC-646] DELETE and INNER JOIN Created: 23/Apr/10  Updated: 03/Jun/13  Resolved: 08/Jun/10

Status: Resolved
Project: Doctrine 1
Component/s: None
Affects Version/s: 1.2.0
Fix Version/s: None

Type: Bug Priority: Major
Reporter: jerome Assignee: Jonathan H. Wage
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

WIndows 7
Wamp (PHP Version 5.3.0 mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.27 $ )



 Description   

I made a DQL delete query.

$q = Doctrine_Query::create()
->delete()
->from('termRelationship tr')
->innerJoin('tr.termTaxonomy tt')
->innerJoin('tr.Post p')
->where('p.id = ?', '1')
->andWhere('tt.taxonomy = ?','category');

//GENERATED SQL OF THE DQL
DELETE FROM term_relationship INNER JOIN term_taxonomy t2 ON t.term_taxonomy_id = t2.id INNER JOIN post p ON t.object_id = p.id WHERE (id = '1' AND taxonomy = 'category')

But this query is incorrect

The query must be( alias are not present beetween DELETE and FROM and for the FROM table)

DELETE tr FROM term_relationship tr INNER JOIN term_taxonomy t2 ON tr.term_taxonomy_id = t2.id INNER JOIN post p ON tr.object_id = p.id WHERE (p.id = '1' AND t2.taxonomy = 'category')

With this request all is good in pphmyadmin.



 Comments   
Comment by Jonathan H. Wage [ 08/Jun/10 ]

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.

Comment by James Bench [ 03/Jun/13 ]

You can't delete from a table when it's in a subquery in MySQL (I'm using Doctrine 2).

DELETE FROM tblA a WHERE a.id NOT IN(SELECT a2.id FROM tblA a2 JOIN a2.tblB b WHERE b.value = :value)
Generated at Sat Oct 25 22:26:23 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.