{"action":"created","issue":{"url":"https://api.github.com/repos/phalcon/cphalcon/issues/2113","labels_url":"https://api.github.com/repos/phalcon/cphalcon/issues/2113/labels{/name}","comments_url":"https://api.github.com/repos/phalcon/cphalcon/issues/2113/comments","events_url":"https://api.github.com/repos/phalcon/cphalcon/issues/2113/events","html_url":"https://github.com/phalcon/cphalcon/issues/2113","id":28489638,"number":2113,"title":"Joining the same table twice","user":{"login":"mariancraciun1983","id":4310442,"avatar_url":"https://avatars.githubusercontent.com/u/4310442?v=3","gravatar_id":"","url":"https://api.github.com/users/mariancraciun1983","html_url":"https://github.com/mariancraciun1983","followers_url":"https://api.github.com/users/mariancraciun1983/followers","following_url":"https://api.github.com/users/mariancraciun1983/following{/other_user}","gists_url":"https://api.github.com/users/mariancraciun1983/gists{/gist_id}","starred_url":"https://api.github.com/users/mariancraciun1983/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mariancraciun1983/subscriptions","organizations_url":"https://api.github.com/users/mariancraciun1983/orgs","repos_url":"https://api.github.com/users/mariancraciun1983/repos","events_url":"https://api.github.com/users/mariancraciun1983/events{/privacy}","received_events_url":"https://api.github.com/users/mariancraciun1983/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/phalcon/cphalcon/labels/Unassigned","name":"Unassigned","color":"fad8c7"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":8,"created_at":"2014-02-28T10:02:25Z","updated_at":"2015-06-05T14:49:15Z","closed_at":null,"body":"I am having problems producing the suitable results when joining the same table twice.\r\n\r\nThe code is the following:\r\n\r\n\r\n```php\r\n$query = \\Zero\\Context\\Zero::instance()->modelsManager()->createBuilder()\r\n      ->columns(\"[RecruitPeople].*,[Recruiter].*, [Converter].*  ,RecruitClass.*, 'Dummy' as Dummy\")\r\n      ->from(array('RecruitPeople'=>'\\Zero\\Models\\RecruitPeople'))\r\n      ->innerJoin('\\Zero\\Models\\RecruitClass','RecruitPeople.class_id = RecruitClass.class_id','RecruitClass')\r\n      ->innerJoin('\\Zero\\Models\\User','RecruitPeople.recruiter_id = Recruiter.user_id','Recruiter')\r\n      ->innerJoin('\\Zero\\Models\\User','RecruitPeople.converter_id = Converter.user_id','Converter');\r\n\r\n```\r\nThe generated Phql looks correct and is:\r\n```sql\r\nSELECT \r\n   RecruitPeople.*,Recruiter.*, Converter.*  ,RecruitClass.*, 'Dummy' as Dummy \r\nFROM \r\n   [\\Zero\\Models\\RecruitPeople] AS [RecruitPeople] \r\n   INNER JOIN [\\Zero\\Models\\RecruitClass] AS [RecruitClass] ON RecruitPeople.class_id = RecruitClass.class_id \r\n   INNER JOIN [\\Zero\\Models\\User] AS [Recruiter] ON RecruitPeople.recruiter_id = Recruiter.user_id \r\n   INNER  JOIN [\\Zero\\Models\\User] AS [Converter] ON RecruitPeople.converter_id = Converter.user_id\r\n```\r\n\r\nThe results is always missing the first alias of the User table. \r\n```php\r\n//This will result in missing \"Recruiter\"\r\n->columns(\"RecruitPeople.*,Recruiter.*, Converter.*  ,RecruitClass.*, 'Dummy' as Dummy\")\r\n```\r\n```php\r\n//This will result in missing \"Converter\"\r\n->columns(\"RecruitPeople.*, Converter.* ,Recruiter.* ,RecruitClass.*, 'Dummy' as Dummy\")\r\n```\r\n\r\n```php\r\n//THis will cause to have Converter overide the results from Recruiter. \r\n->innerJoin('\\Zero\\Models\\User','RecruitPeople.converter_id = Converter.user_id','Converter')\r\n```\r\n\r\n\r\n\r\nThe idea is that RecruitPeople can be converter or recruited by a User, so i need to join twice with User.\r\n\r\n\r\nI know that in the docs, it's being said that there are some issues with the columns renaming,aliases, but not with tables.\r\n\r\nI though about extending the model User to make this properly work, but it's not ideal.\r\n\r\n\r\n\r\n\r\n<bountysource-plugin>\r\n---\r\nWant to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/1896373-joining-the-same-table-twice?utm_campaign=plugin&utm_content=tracker%2F50707&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F50707&utm_medium=issues&utm_source=github).\r\n</bountysource-plugin>"},"comment":{"url":"https://api.github.com/repos/phalcon/cphalcon/issues/comments/109317549","html_url":"https://github.com/phalcon/cphalcon/issues/2113#issuecomment-109317549","issue_url":"https://api.github.com/repos/phalcon/cphalcon/issues/2113","id":109317549,"user":{"login":"marianacapelo","id":3236387,"avatar_url":"https://avatars.githubusercontent.com/u/3236387?v=3","gravatar_id":"","url":"https://api.github.com/users/marianacapelo","html_url":"https://github.com/marianacapelo","followers_url":"https://api.github.com/users/marianacapelo/followers","following_url":"https://api.github.com/users/marianacapelo/following{/other_user}","gists_url":"https://api.github.com/users/marianacapelo/gists{/gist_id}","starred_url":"https://api.github.com/users/marianacapelo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/marianacapelo/subscriptions","organizations_url":"https://api.github.com/users/marianacapelo/orgs","repos_url":"https://api.github.com/users/marianacapelo/repos","events_url":"https://api.github.com/users/marianacapelo/events{/privacy}","received_events_url":"https://api.github.com/users/marianacapelo/received_events","type":"User","site_admin":false},"created_at":"2015-06-05T14:49:15Z","updated_at":"2015-06-05T14:49:15Z","body":"@andresgutierrez Does this work?\r\n\r\n```\r\n\r\nclass DbProfiler\r\n{\r\n\r\n    protected $_profiler;\r\n    protected $_logger;\r\n\r\n    /**\r\n     * Creates the profiler and starts the logging\r\n     */\r\n    public function __construct()\r\n    {\r\n        $this->_profiler = new \\Phalcon\\Db\\Profiler();\r\n        $this->_logger   = new \\Phalcon\\Logger\\Adapter\\File(\"db.log\");\r\n    }\r\n\r\n    /**\r\n     * This is executed if the event triggered is 'beforeQuery'\r\n     */\r\n    public function beforeQuery($event, $connection)\r\n    {\r\n        $this->_profiler->startProfile($connection->getSQLStatement());\r\n    }\r\n\r\n    /**\r\n     * This is executed if the event triggered is 'afterQuery'\r\n     */\r\n    public function afterQuery($event, $connection)\r\n    {\r\n        $this->_logger->log($connection->getSQLStatement(), \\Phalcon\\Logger::INFO);\r\n        $this->_profiler->stopProfile();\r\n    }\r\n}\r\n\r\n\r\nclass Test1 extends \\Phalcon\\Mvc\\Model\r\n{\r\n\r\n    public $id;\r\n    public $fk_1;\r\n    public $fk_2;\r\n    public $name;\r\n\r\n    public function getSource()\r\n    {\r\n        return 'test_1';\r\n    }\r\n}\r\n\r\n\r\nclass Test2 extends \\Phalcon\\Mvc\\Model\r\n{\r\n\r\n    public $id;\r\n    public $name;\r\n\r\n    public function getSource()\r\n    {\r\n        return 'test_2';\r\n    }\r\n\r\n}\r\n\r\n\r\n\r\n$di = new \\Phalcon\\DI();\r\n$di->set('db', function ()  {\r\n\r\n    $config = new Phalcon\\Config\\Adapter\\Ini(\"config.ini\");\r\n\r\n    $connection = new Phalcon\\Db\\Adapter\\Pdo\\Mysql(\r\n        array(\r\n            \"host\" => $config->database->host,\r\n            \"username\" => $config->database->username,\r\n            \"password\" => $config->database->password,\r\n            \"dbname\" => $config->database->dbname\r\n        )\r\n    );\r\n\r\n\r\n\r\n    $eventsManager = new \\Phalcon\\Events\\Manager();\r\n    $db_profiler = new \\DbProfiler();\r\n    $eventsManager->attach('db',$db_profiler);\r\n    $connection->setEventsManager($eventsManager);\r\n\r\n\r\n    return $connection;\r\n}, true);\r\n\r\n$di->set(\r\n    'modelsManager',\r\n    function()\r\n    {\r\n        $modelsManager = new  \\Phalcon\\Mvc\\Model\\Manager();\r\n        $eventsManager = new Phalcon\\Events\\Manager();\r\n\r\n        $modelsManager->setEventsManager($eventsManager);\r\n\r\n        return $modelsManager;\r\n    }\r\n);\r\n\r\n$di->set(\r\n    'modelsMetadata',\r\n    function()\r\n    {\r\n        $metaData = new Phalcon\\Mvc\\Model\\MetaData\\Memory();\r\n        return $metaData;\r\n    }\r\n);\r\n\r\n\r\n/**\r\n * Query builder fetching the models\r\n */\r\n\r\n$models_manager = $di->get('modelsManager');\r\n$q = $models_manager->createBuilder()\r\n    ->columns('t1.*, t2_1.*, t2_2.*')\r\n    ->addFrom('Test1','t1')\r\n    ->join(\r\n        'Test2',\r\n        't2_1.id = t1.fk_1',\r\n        't2_1'\r\n    )\r\n    ->join(\r\n        'Test2',\r\n        't2_2.id = t1.fk_2',\r\n        't2_2'\r\n    )\r\n    ->getQuery();\r\n\r\n$row = $q->getSingleResult();\r\n\r\nprint_r($row->t1_1);\r\n\r\n```\r\n\r\nDatabase simple script\r\n```\r\n\r\nCREATE TABLE IF NOT EXISTS `test_2` (\r\n  `id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `name` varchar(45) NOT NULL,\r\n  PRIMARY KEY (`id`)\r\n) ENGINE=InnoDB  DEFAULT CHARSET=utf8;\r\n\r\nINSERT INTO `test_2` (`name`) VALUES\r\n  ('first id');\r\n\r\nINSERT INTO `test_2` (`name`) VALUES\r\n  ('second id');\r\n\r\nCREATE TABLE IF NOT EXISTS `test_1` (\r\n  `id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `fk_1` int(11) NOT NULL,\r\n  `fk_2` int(11) NOT NULL,\r\n  `name` varchar(45) NOT NULL,\r\n  PRIMARY KEY (`id`)\r\n) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;\r\n\r\nINSERT INTO `test_1` (`fk_1`, `fk_2`, `name`) VALUES\r\n  (1,2,'hi!');\r\n```\r\n\r\n"}}