{"action":"created","issue":{"url":"https://api.github.com/repos/yiisoft/yii2/issues/9266","labels_url":"https://api.github.com/repos/yiisoft/yii2/issues/9266/labels{/name}","comments_url":"https://api.github.com/repos/yiisoft/yii2/issues/9266/comments","events_url":"https://api.github.com/repos/yiisoft/yii2/issues/9266/events","html_url":"https://github.com/yiisoft/yii2/issues/9266","id":98221271,"number":9266,"title":"Impossible to use ActiveQuery through findBySql in subquery.","user":{"login":"RoelofRuis","id":8556376,"avatar_url":"https://avatars.githubusercontent.com/u/8556376?v=3","gravatar_id":"","url":"https://api.github.com/users/RoelofRuis","html_url":"https://github.com/RoelofRuis","followers_url":"https://api.github.com/users/RoelofRuis/followers","following_url":"https://api.github.com/users/RoelofRuis/following{/other_user}","gists_url":"https://api.github.com/users/RoelofRuis/gists{/gist_id}","starred_url":"https://api.github.com/users/RoelofRuis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/RoelofRuis/subscriptions","organizations_url":"https://api.github.com/users/RoelofRuis/orgs","repos_url":"https://api.github.com/users/RoelofRuis/repos","events_url":"https://api.github.com/users/RoelofRuis/events{/privacy}","received_events_url":"https://api.github.com/users/RoelofRuis/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/yiisoft/yii2/labels/type:docs","name":"type:docs","color":"207de5"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-07-30T17:27:49Z","updated_at":"2015-09-19T15:41:08Z","closed_at":null,"body":"It seems impossible to use an `ActiveQuery` instantiated through calling `ActiveRecord::findBySql()` as a subquery.\r\n\r\nI've reduced the problem to its basic form in this function:\r\n```php\r\npublic function testFindBySql() {\r\n    $user_subquery = ActiveRecord::findBySql('SELECT * FROM {{%user}} WHERE [[last_name]] ILIKE :lastname');\r\n    $user_subquery->addParams([':lastname' => 'smith']);\r\n\r\n    $query = new Query();\r\n    $query->from(['users' => $user_subquery]);\r\n\r\n    printf(\"Subquery SQL:\\t%s\\nMain query SQL:\\t%s\",\r\n            $user_subquery->createCommand()->getRawSql(),\r\n            $query->createCommand()->getRawSql());\r\n}\r\n```\r\nThis outputs\r\n```\r\nSubquery SQL:\tSELECT * FROM \"tbl_user\" WHERE \"last_name\" ILIKE 'smith'\r\nMain query SQL:\tSELECT * FROM (SELECT * FROM \"tbl_active_record\") \"users\"\r\n```\r\nAlthough I would expect the inner select statement in the main query SQL to contain the complete Subquery SQL.\r\n\r\nIt seems like the subquery is always constructed from the standard query attributes, but it seems desirable to be able to use it in the way described above."},"comment":{"url":"https://api.github.com/repos/yiisoft/yii2/issues/comments/141681362","html_url":"https://github.com/yiisoft/yii2/issues/9266#issuecomment-141681362","issue_url":"https://api.github.com/repos/yiisoft/yii2/issues/9266","id":141681362,"user":{"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},"created_at":"2015-09-19T15:41:08Z","updated_at":"2015-09-19T15:41:08Z","body":"there is no need to wrap the SQL in `findBySql()`. ` findBySql()` is only useful if you want to compose plain SQL to retrieve records. for a subquery you can just use a simple string.\r\n\r\nI have adjusted the docs for it and added a test."}}