Doctrine 1
  1. Doctrine 1
  2. DC-25

Joining the same m2m relation more than one time produces query with duplicated aliases


    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 1.1.4
    • Fix Version/s: None
    • Component/s: Query
    • Labels:


      moved my ticket #1941 from trac

      Let's consider an environment:

      We have a table "Article" and another table "Tag". There is a many to many relation between those two tables via link table "TagArticle". Schema and fixtures provided in attachments. It is simple.

      Now let's consider a situation:

      We want to display a list of all articles with on of tags. But we want to display this list also with the rest of tags connected to articles with specified tag:

      Article1: tag1, tag2, tag3, tag4
      Article2: tag2, tag3
      Article3: tag4

      We want a list of all articles with "tag2" - so it will be Article1 and Article2, but the same query should also retrieve the rest of tags.

      It is really simple to do with one-to-many relation: by just joining two tables with different aliases.

      However, it is impossible now to do it with many-to-many relation, because Doctrine generates wrong table aliases in SQL.

      $query = Doctrine_Query::create()->select(', tg.*')
          ->from('Article a')
          ->leftJoin('a.Tags tg')
          ->innerJoin('a.Tags tg2 WITH = ?', 'tag2')

      Looks logical. But it throws an exception:

      "Doctrine_Connection_Mysql_Exception" with message "SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 't2'" in ...doctrine\lib\DoctrineConnection.php:1084

      Let's look at our query:

      SELECT AS a__id, AS t__id, AS t__name 
      FROM article a 
      LEFT JOIN tag_article t2 ON = t2.article_id 
      LEFT JOIN tag t ON = t2.tag_id 
      INNER JOIN tag_article t2 ON = t2.article_id 
      INNER JOIN tag t3 ON = t2.tag_id AND = ?

      As we can see, there is a duplicated alias on "tag_article".


        Guilherme Blanco added a comment -

        This issue was fixed in r6407

        Guilherme Blanco added a comment - This issue was fixed in r6407


          • Assignee:
            Roman S. Borschel
            Jacek Jędrzejewski
          • Votes:
            0 Vote for this issue
            1 Start watching this issue


            • Created: