{"action":"created","issue":{"url":"https://api.github.com/repos/web2py/pydal/issues/407","repository_url":"https://api.github.com/repos/web2py/pydal","labels_url":"https://api.github.com/repos/web2py/pydal/issues/407/labels{/name}","comments_url":"https://api.github.com/repos/web2py/pydal/issues/407/comments","events_url":"https://api.github.com/repos/web2py/pydal/issues/407/events","html_url":"https://github.com/web2py/pydal/issues/407","id":171023905,"number":407,"title":"Joinable subselects","user":{"login":"nextghost","id":1794396,"avatar_url":"https://avatars.githubusercontent.com/u/1794396?v=3","gravatar_id":"","url":"https://api.github.com/users/nextghost","html_url":"https://github.com/nextghost","followers_url":"https://api.github.com/users/nextghost/followers","following_url":"https://api.github.com/users/nextghost/following{/other_user}","gists_url":"https://api.github.com/users/nextghost/gists{/gist_id}","starred_url":"https://api.github.com/users/nextghost/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nextghost/subscriptions","organizations_url":"https://api.github.com/users/nextghost/orgs","repos_url":"https://api.github.com/users/nextghost/repos","events_url":"https://api.github.com/users/nextghost/events{/privacy}","received_events_url":"https://api.github.com/users/nextghost/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/web2py/pydal/labels/proposal","name":"proposal","color":"fef2c0"}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":5,"created_at":"2016-08-13T19:35:25Z","updated_at":"2016-08-23T14:16:20Z","closed_at":null,"body":"Subselects are a really powerful tool in SQL and PyDAL currently supports only a tiny fraction of their true power. I plan to fix that soon so I'm opening this thread for design discussion.\r\n\r\nThe obvious way to go is to make `db()._select()` return a table-like class instance that can then be executed as a \"prepared statement\" on its own or reused in other queries. It's also a necessary step for passing values properly through placeholders. The class will need to implement these methods:\r\n\r\n  * `__getitem__()` - only for accessing public fields by name; it won't do any DB queries\r\n  * `__call__()` - execute the prepared query and return any results\r\n  * `__setattr__()`\r\n  * `__iter__()`\r\n  * `__repr__()`\r\n  * `__str__()` - the query string (it'll raise exception if the query references external tables)\r\n  * `fields()` - public fields will be instances of FieldVirtual with the same name and type as the underlying query field/expression; nameless expressions will be excluded\r\n  * `as_dict()`\r\n  * `with_alias()`\r\n  * `on()`\r\n  * `_filter_fields()`\r\n  * another method that will internally generate the query string, with optional parameter for a list of external tables\r\n  * `ALL` - expands to the list of public fields\r\n\r\nSome open questions:\r\n  * Should there be some public interface for accessing the query fields/expressions? (Meaning the field/expression arguments passed to `db()._select()`, in addition to their FieldVirtual representatives.)\r\n  * Should the list of public fields get populated only when `with_alias()` was called? (Subquery must always be aliased in a join.)\r\n  * Any suggestions for passing subqueries to `table.insert()`? The mapping between subquery result and destination table is done by position, not by name."},"comment":{"url":"https://api.github.com/repos/web2py/pydal/issues/comments/241745167","html_url":"https://github.com/web2py/pydal/issues/407#issuecomment-241745167","issue_url":"https://api.github.com/repos/web2py/pydal/issues/407","id":241745167,"user":{"login":"nextghost","id":1794396,"avatar_url":"https://avatars.githubusercontent.com/u/1794396?v=3","gravatar_id":"","url":"https://api.github.com/users/nextghost","html_url":"https://github.com/nextghost","followers_url":"https://api.github.com/users/nextghost/followers","following_url":"https://api.github.com/users/nextghost/following{/other_user}","gists_url":"https://api.github.com/users/nextghost/gists{/gist_id}","starred_url":"https://api.github.com/users/nextghost/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nextghost/subscriptions","organizations_url":"https://api.github.com/users/nextghost/orgs","repos_url":"https://api.github.com/users/nextghost/repos","events_url":"https://api.github.com/users/nextghost/events{/privacy}","received_events_url":"https://api.github.com/users/nextghost/received_events","type":"User","site_admin":false},"created_at":"2016-08-23T14:16:20Z","updated_at":"2016-08-23T14:16:20Z","body":"Talk is cheap so I've pushed some code as pull request #408. We should continue there."}}
{"actor":{"display_login":"nextghost"}}