Skip to content

Loading…

DDC-1161: DQL generate duplicate SQL Alias with CTI + oneToOne self referencing #1763

Closed
doctrinebot opened this Issue · 6 comments

2 participants

@doctrinebot

Jira issue originally created by user nico_b:

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,

@doctrinebot

Comment created by @beberlei:

Fixed formatting.

@doctrinebot

Comment created by @beberlei:

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

@doctrinebot

Comment created by nico_b:

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.

@doctrinebot

Comment created by @guilhermeblanco:

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: 33bcf7a

Marking the ticket as fixed in 2.2

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added this to the 2.2 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.