{"action":"created","issue":{"url":"https://api.github.com/repos/yiisoft/yii/issues/1588","labels_url":"https://api.github.com/repos/yiisoft/yii/issues/1588/labels{/name}","comments_url":"https://api.github.com/repos/yiisoft/yii/issues/1588/comments","events_url":"https://api.github.com/repos/yiisoft/yii/issues/1588/events","html_url":"https://github.com/yiisoft/yii/issues/1588","id":7735858,"number":1588,"title":"Not unique table/alias mySql exception with two through relations on another through relation","user":{"login":"sieppl","id":837595,"avatar_url":"https://avatars.githubusercontent.com/u/837595?v=3","gravatar_id":"","url":"https://api.github.com/users/sieppl","html_url":"https://github.com/sieppl","followers_url":"https://api.github.com/users/sieppl/followers","following_url":"https://api.github.com/users/sieppl/following{/other_user}","gists_url":"https://api.github.com/users/sieppl/gists{/gist_id}","starred_url":"https://api.github.com/users/sieppl/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sieppl/subscriptions","organizations_url":"https://api.github.com/users/sieppl/orgs","repos_url":"https://api.github.com/users/sieppl/repos","events_url":"https://api.github.com/users/sieppl/events{/privacy}","received_events_url":"https://api.github.com/users/sieppl/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/yiisoft/yii/labels/feature:AR","name":"feature:AR","color":"0b02e1"},{"url":"https://api.github.com/repos/yiisoft/yii/labels/type:bug","name":"type:bug","color":"e102d8"}],"state":"open","locked":false,"assignee":{"login":"cebe","id":189796,"avatar_url":"https://avatars.githubusercontent.com/u/189796?v=3","gravatar_id":"","url":"https://api.github.com/users/cebe","html_url":"https://github.com/cebe","followers_url":"https://api.github.com/users/cebe/followers","following_url":"https://api.github.com/users/cebe/following{/other_user}","gists_url":"https://api.github.com/users/cebe/gists{/gist_id}","starred_url":"https://api.github.com/users/cebe/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cebe/subscriptions","organizations_url":"https://api.github.com/users/cebe/orgs","repos_url":"https://api.github.com/users/cebe/repos","events_url":"https://api.github.com/users/cebe/events{/privacy}","received_events_url":"https://api.github.com/users/cebe/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/yiisoft/yii/milestones/9","html_url":"https://github.com/yiisoft/yii/milestones/1.1.17","labels_url":"https://api.github.com/repos/yiisoft/yii/milestones/9/labels","id":399649,"number":9,"title":"1.1.17","description":"","creator":{"login":"samdark","id":47294,"avatar_url":"https://avatars.githubusercontent.com/u/47294?v=3","gravatar_id":"","url":"https://api.github.com/users/samdark","html_url":"https://github.com/samdark","followers_url":"https://api.github.com/users/samdark/followers","following_url":"https://api.github.com/users/samdark/following{/other_user}","gists_url":"https://api.github.com/users/samdark/gists{/gist_id}","starred_url":"https://api.github.com/users/samdark/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/samdark/subscriptions","organizations_url":"https://api.github.com/users/samdark/orgs","repos_url":"https://api.github.com/users/samdark/repos","events_url":"https://api.github.com/users/samdark/events{/privacy}","received_events_url":"https://api.github.com/users/samdark/received_events","type":"User","site_admin":false},"open_issues":171,"closed_issues":40,"state":"open","created_at":"2013-08-12T10:14:01Z","updated_at":"2015-04-02T18:08:03Z","due_on":null,"closed_at":null},"comments":10,"created_at":"2012-10-20T08:31:12Z","updated_at":"2015-04-07T04:56:27Z","closed_at":null,"body":"Let's say we have Author, Category, AuthorHasCategory. Each author has a main category he is working on. Therefore the table \"AuthorHasCategory\" has a boolean \"is_main\". Category has n:1 relations to Foo and Bar to further describe the Category.\r\n\r\nNow we define following relations on Author:\r\n\r\n\tpublic function relations() {\r\n\t\treturn array(\r\n\t\t\t\t'authorHasCategories' => array(self::HAS_MANY, 'AuthorHasCategory', 'author_id'),\r\n\t\t\t\t'mainCategory'=>array(self::HAS_ONE,'Category',array('category_id'=>'id'),'through'=>'authorHasCategories', 'on' => 'is_main = 1'),\r\n\t\t\t\t'mainCategoryFoo' => array(self::HAS_ONE, 'Foo', array('foo_id' => 'id'), 'through' => 'mainCategory'),\r\n\t\t\t\t//BUG: adding following declaration will not work due to a duplicate table alias exception from MySql\r\n\t\t\t\t'mainCategoryBar' => array(self::HAS_ONE, 'Bar', array('bar_id' => 'id'), 'through' => 'mainCategory')\r\n\t\t);\r\n\t}\r\n\r\nWhen mainCategoryFoo and mainCategoryBar are **added to \"with\" at the same time**, an mysql exception occurs:\r\n\r\n    SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'authorHasCategories'.\r\n\r\nWhen checking the sql query you will find twice:\r\n\r\n     LEFT OUTER JOIN `author_has_category` `authorHasCategories` ON (`authorHasCategories`.`author_id`=`t`.`id`)\r\n\r\nThe first time for the actual pivot table and another time it seems the whole left other join chain is added another time instead of reuse existing joins.\r\n\r\nSorry for the abstract example, I had to cut it out from our current project."},"comment":{"url":"https://api.github.com/repos/yiisoft/yii/issues/comments/90358400","html_url":"https://github.com/yiisoft/yii/issues/1588#issuecomment-90358400","issue_url":"https://api.github.com/repos/yiisoft/yii/issues/1588","id":90358400,"user":{"login":"PatchRanger","id":3889569,"avatar_url":"https://avatars.githubusercontent.com/u/3889569?v=3","gravatar_id":"","url":"https://api.github.com/users/PatchRanger","html_url":"https://github.com/PatchRanger","followers_url":"https://api.github.com/users/PatchRanger/followers","following_url":"https://api.github.com/users/PatchRanger/following{/other_user}","gists_url":"https://api.github.com/users/PatchRanger/gists{/gist_id}","starred_url":"https://api.github.com/users/PatchRanger/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/PatchRanger/subscriptions","organizations_url":"https://api.github.com/users/PatchRanger/orgs","repos_url":"https://api.github.com/users/PatchRanger/repos","events_url":"https://api.github.com/users/PatchRanger/events{/privacy}","received_events_url":"https://api.github.com/users/PatchRanger/received_events","type":"User","site_admin":false},"created_at":"2015-04-07T04:56:27Z","updated_at":"2015-04-07T04:56:27Z","body":"> the query does not use the unique aliases, it uses the first ('uziv_2') only\r\n\r\nConfirmed, it always takes the first alias - so the resulting select is not such as expected."}}