Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1161

DQL generate duplicate SQL Alias with CTI + oneToOne self referencing

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.5, 2.1
    • Fix Version/s: 2.2
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None
    • Environment:
      WAMP , PHP 5.3.5 , MySql 5.5.8
      Symfony 2 Beta 1

      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,

      1. Content.php
        0.9 kB
        Nicolas Badey
      2. Page.php
        0.3 kB
        Nicolas Badey

        Activity

        Hide
        Guilherme Blanco added a comment -

        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

        Show
        Guilherme Blanco added a comment - 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
        Hide
        Nicolas Badey added a comment - - edited

        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.

        Show
        Nicolas Badey added a comment - - edited 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.
        Hide
        Benjamin Eberlei added a comment -

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

        Show
        Benjamin Eberlei added a comment - Can you upload the mapping files and php code? This looks very weird and i dont know where to start debugging from your description.
        Hide
        Benjamin Eberlei added a comment -

        Fixed formatting.

        Show
        Benjamin Eberlei added a comment - Fixed formatting.

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Nicolas Badey
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: