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

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".