{"action":"created","issue":{"url":"https://api.github.com/repos/coleifer/peewee/issues/733","labels_url":"https://api.github.com/repos/coleifer/peewee/issues/733/labels{/name}","comments_url":"https://api.github.com/repos/coleifer/peewee/issues/733/comments","events_url":"https://api.github.com/repos/coleifer/peewee/issues/733/events","html_url":"https://github.com/coleifer/peewee/issues/733","id":112467864,"number":733,"title":"Problem with alias when joining subquery","user":{"login":"srolija","id":307150,"avatar_url":"https://avatars.githubusercontent.com/u/307150?v=3","gravatar_id":"","url":"https://api.github.com/users/srolija","html_url":"https://github.com/srolija","followers_url":"https://api.github.com/users/srolija/followers","following_url":"https://api.github.com/users/srolija/following{/other_user}","gists_url":"https://api.github.com/users/srolija/gists{/gist_id}","starred_url":"https://api.github.com/users/srolija/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/srolija/subscriptions","organizations_url":"https://api.github.com/users/srolija/orgs","repos_url":"https://api.github.com/users/srolija/repos","events_url":"https://api.github.com/users/srolija/events{/privacy}","received_events_url":"https://api.github.com/users/srolija/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":6,"created_at":"2015-10-20T21:50:13Z","updated_at":"2015-10-23T21:48:26Z","closed_at":"2015-10-21T01:41:50Z","body":"Maybe I assume wrong that following sould work, but there seems to be a issue with alias when I join subquery with table. Since entire subquery will be inserted in SELECT and JOIN clause but should be only in JOIN. Following is simplified version that includes most basic form where I could show problem.\r\n\r\n```\r\nfrom peewee import Model, CharField, DateTimeField, ForeignKeyField, BooleanField\r\nimport peewee\r\n\r\ndatabase = peewee.SqliteDatabase(\"data2.db\")\r\n\r\n\r\nclass BaseModel(Model):\r\n    class Meta:\r\n        database = database\r\n\r\nclass Ticket(BaseModel):\r\n    code = CharField(max_length=16, index=True)\r\n\r\nclass Scan(BaseModel):\r\n    code = CharField(max_length=24)\r\n    confirmed = BooleanField(default=True)\r\n\r\ndatabase.create_tables([Ticket, Scan])\r\nTicket.create(code=\"a\")\r\nTicket.create(code=\"b\")\r\nTicket.create(code=\"c\")\r\nTicket.create(code=\"d\")\r\nScan.create(code=\"a\", confirmed=True)\r\nScan.create(code=\"a\", confirmed=False)\r\nScan.create(code=\"a\", confirmed=False)\r\nScan.create(code=\"b\", confirmed=False)\r\nScan.create(code=\"b\", confirmed=False)\r\nScan.create(code=\"c\", confirmed=True)\r\n\r\nsq = Scan.select(Scan).where(Scan.confirmed == True).group_by(Scan.code)\r\nsq = sq.alias(\"x\")\r\nq = Ticket.select(Ticket, sq) \\\r\n          .join(sq, peewee.JOIN.LEFT_OUTER, on=(Ticket.code == sq.c.code).alias(\"scan\")) \\\r\n          .group_by(Ticket.code)\r\n\r\nfor t in q:\r\n    print(t.code + \": \" + str(t.scan.confirmed))\r\n```\r\n\r\nMy attempt to fix it generates SQL that returns expected result set (all tickets joined only with confirmed scans), but it is not possible to access joined data (via t.scan, results in AttributeError) which in real models contain additional fields.\r\n\r\n```\r\nq = Ticket.select(peewee.SQL(\"*\")) \\\r\n          .join(sq, peewee.JOIN.LEFT_OUTER, on=(Ticket.code == sq.c.code).alias(\"scan\")) \\\r\n          .group_by(Ticket.code)\r\n```\r\n\r\nWhile I would expect it to output:\r\n\r\n```\r\na: True\r\nb: None\r\nc: True\r\nd: None\r\n```\r\nMeanwhile following works as expected, but query is not the one I want, since it could do join a with scan whose confirmed=False while there do exist some with confirmed=True:\r\n\r\n```\r\nq = Ticket.select(Ticket, Scan) \\\r\n          .join(Scan, peewee.JOIN.LEFT_OUTER, on=(Ticket.code == Scan.code).alias(\"scan\")) \\\r\n          .group_by(Ticket.code)\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/coleifer/peewee/issues/comments/150701209","html_url":"https://github.com/coleifer/peewee/issues/733#issuecomment-150701209","issue_url":"https://api.github.com/repos/coleifer/peewee/issues/733","id":150701209,"user":{"login":"srolija","id":307150,"avatar_url":"https://avatars.githubusercontent.com/u/307150?v=3","gravatar_id":"","url":"https://api.github.com/users/srolija","html_url":"https://github.com/srolija","followers_url":"https://api.github.com/users/srolija/followers","following_url":"https://api.github.com/users/srolija/following{/other_user}","gists_url":"https://api.github.com/users/srolija/gists{/gist_id}","starred_url":"https://api.github.com/users/srolija/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/srolija/subscriptions","organizations_url":"https://api.github.com/users/srolija/orgs","repos_url":"https://api.github.com/users/srolija/repos","events_url":"https://api.github.com/users/srolija/events{/privacy}","received_events_url":"https://api.github.com/users/srolija/received_events","type":"User","site_admin":false},"created_at":"2015-10-23T21:48:26Z","updated_at":"2015-10-23T21:48:26Z","body":"Fantastic, that was exactly what I wanted to do, but did not know that it is possible to use alias on multiple conditions when using it as join on parameter.\r\n\r\nThank you very much!"}}