Doctrine 1
  1. Doctrine 1
  2. DC-25

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

    Details

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

      Description

      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('a.id, tg.*')
          ->from('Article a')
          ->leftJoin('a.Tags tg')
          ->innerJoin('a.Tags tg2 WITH tg2.name = ?', '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 a.id AS a__id, t.id AS t__id, t.name AS t__name 
      FROM article a 
      LEFT JOIN tag_article t2 ON a.id = t2.article_id 
      LEFT JOIN tag t ON t.id = t2.tag_id 
      INNER JOIN tag_article t2 ON a.id = t2.article_id 
      INNER JOIN tag t3 ON t3.id = t2.tag_id AND t3.name = ?
      

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

        Activity

        Hide
        Guilherme Blanco added a comment -

        This issue was fixed in r6407

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

          People

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

            Dates

            • Created:
              Updated:
              Resolved: