[DDC-1770] Select from multiple subclassed entities generates SQL syntax error Created: 05/Apr/12  Updated: 27/May/12  Resolved: 27/May/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.1.6
Fix Version/s: 2.1.7
Security Level: All

Type: Bug Priority: Major
Reporter: Nicolas Laplante Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Symfony 2.0.12
PHP 5.3.10
MySQL 5.1


Attachments: File Comment.php     File CommentHistoryItem.php     File HistoryItem.php     File Photo.php     File PhotoHistoryItem.php    

 Description   

I have 2 entities subclassing a "master" entity using single table inheritance:

HistoryItem:
createdAt datetime

PhotoHistoryItem extends HistoryItem
photo: (foreign key to Photo entity)

CommentHistoryItem extends HistoryItem
comment: (foreign key to Comment entity)

I want to select all of them using the following DQL:

SELECT p, c FROM ProFolioBundle:PhotoHistoryItem p, ProFolioBundle:CommentHistoryItem c LEFT JOIN p.photo LEFT JOIN c.comment

But the generated SQL has an error in the final AND syntax (it generates "AND AND"):

SELECT h0_.id AS id0, h0_.createdAt AS createdAt1, h1_.id AS id2, h1_.createdAt AS createdAt3, h0_.type AS type4, h1_.type AS type5 FROM HistoryItem h0_, HistoryItem h1_ LEFT JOIN Photo p2_ ON h0_.photo_id = p2_.id INNER JOIN Comment c3_ ON h1_.comment_id = c3_.id WHERE (h0_.type IN ('photo') AND AND h1_.type IN ('comment')) LIMIT 20

The error MySQL throws is:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND h1_.type IN ('comment')) LIMIT 20' at line 1



 Comments   
Comment by Nicolas Laplante [ 05/Apr/12 ]

Fix entity names

Comment by Fabio B. Silva [ 17/Apr/12 ]

Hi Nicolas

Could you attach your entities please ?

Comment by Nicolas Laplante [ 17/Apr/12 ]

Sure, here are the 3 entities

Comment by Nicolas Laplante [ 17/Apr/12 ]

Here are the referenced entities in case you need them too

Comment by Benjamin Eberlei [ 27/May/12 ]

Fixed

Generated at Thu Jul 31 18:00:51 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.