[DDC-1161] DQL generate duplicate SQL Alias with CTI + oneToOne self referencing Created: 20/May/11  Updated: 16/Oct/11  Resolved: 16/Oct/11

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.0.5, 2.1
Fix Version/s: 2.2
Security Level: All

Type: Bug Priority: Major
Reporter: Nicolas Badey Assignee: Guilherme Blanco
Resolution: Fixed Votes: 0
Labels: None
Environment:

WAMP , PHP 5.3.5 , MySql 5.5.8
Symfony 2 Beta 1


Attachments: File Content.php     File Page.php    

 Description   

Hi,

I have a Entity "Content" whith several childs entities like "Page" , "Article" in Joined inheritance.
I make DQL directly on entity "Content", that work perfectly with the discriminator map for return/delete/update appropriates objects.
I use "Page" or "Article" entity only for make a new object and persist.

But now I have add a oneToOne self relation in "Content" :
Content#parent_id => Content#id , no cascade.

And now every DQL return :
SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: XX
And yes I see in generated SQL that Content's alias is not unique :

For DQL : SELECT c FROM Content WHERE c.status = 1

SQL Result :

SELECT n0_.id AS id0, n0_.name AS name1, n0_.title AS title2, n0_.author AS author3, n0_.author_update AS author_update4, n0_.status AS status5, n0_.type AS type6, n0_.weight AS weight7, n0_.create_on AS create_on8, n0_.update_on AS update_on9, n0_.url AS url10, n0_.zone AS zone11, n0_.children_sort AS children_sort12, n0_.children_sort_type AS children_sort_type13, n0_.path AS path14, n0_.level AS level15, n1_.meta AS meta16, n2_.content AS content17, n3_.description AS description18, n3_.keywords AS keywords19, n3_.gwt AS gwt20, n3_.analytics AS analytics21, n3_.xiti AS xiti22, n0_.class AS class23 FROM ncms_content n0_ LEFT JOIN ncms_page n1_ ON n0_.id = n1_.id LEFT JOIN ncms_article n2_ ON n0_.id = n2_.id LEFT JOIN ncms_content n0_ ON n0_.id = n0_.id LEFT JOIN ncms_content n0_ ON n0_.id = n0_.id LEFT JOIN ncms_content n0_ ON n0_.id = n0_.id LEFT JOIN ncms_content n0_ ON n0_.id = n0_.id LEFT JOIN ncms_content n0_ ON n0_.id = n0_.id LEFT JOIN ncms_website n3_ ON n0_.id = n3_.id LEFT JOIN ncms_content n0_ ON n0_.id = n0_.id WHERE n0_.status =1

=> SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'n0_'

But if I use getRepository like this :
$em->getRepository('Content')->findBy(array('status'=>1));
It works, and return appropriates objects.
And I can see that "Content" have several unique alias in the SQL query :

SELECT t0.id AS id1, t0.name AS name2, t0.title AS title3, t0.author AS author4, t0.author_update AS author_update5, t0.status AS status6, t0.type AS type7, t0.weight AS weight8, t0.create_on AS create_on9, t0.update_on AS update_on10, t0.url AS url11, t0.zone AS zone12, t0.children_sort AS children_sort13, t0.children_sort_type AS children_sort_type14, t0.path AS path15, t0.level AS level16, t0.parent_id AS parent_id17, t0.class, t18.meta AS meta19, t20.content AS content21, t27.description AS description28, t27.keywords AS keywords29, t27.gwt AS gwt30, t27.analytics AS analytics31, t27.xiti AS xiti32 FROM ncms_content t0 LEFT JOIN ncms_page t18 ON t0.id = t18.id LEFT JOIN ncms_article t20 ON t0.id = t20.id LEFT JOIN ncms_content t22 ON t0.id = t22.id LEFT JOIN ncms_content t23 ON t0.id = t23.id LEFT JOIN ncms_content t24 ON t0.id = t24.id LEFT JOIN ncms_content t25 ON t0.id = t25.id LEFT JOIN ncms_content t26 ON t0.id = t26.id LEFT JOIN ncms_website t27 ON t0.id = t27.id LEFT JOIN ncms_content t33 ON t0.id = t33.id WHERE t0.status =1

Regards,



 Comments   
Comment by Benjamin Eberlei [ 05/Jun/11 ]

Fixed formatting.

Comment by Benjamin Eberlei [ 05/Jun/11 ]

Can you upload the mapping files and php code? This looks very weird and i dont know where to start debugging from your description.

Comment by Nicolas Badey [ 06/Jun/11 ]

Done, Mapping and Entities (simplified).

I have a little question, how we can acces to the discriminator map in a DQL query or Entity's object ? Because we cannot mappe this field.

Comment by Guilherme Blanco [ 16/Oct/11 ]

Hi Nicholas,

In 2.2-DEV this issue seems to be addressed already.
We did an internal refactoring (that leads us to not merge into 2.1) that addressed this issue.

I added coverage to your issue with this commit and it works nicely: https://github.com/doctrine/doctrine2/commit/33bcf7ad6f67b8641983f51901d0e74cfde8446c

Marking the ticket as fixed in 2.2

Generated at Fri Oct 24 21:52:42 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.