{"action":"created","issue":{"url":"https://api.github.com/repos/yiisoft/yii2/issues/12111","repository_url":"https://api.github.com/repos/yiisoft/yii2","labels_url":"https://api.github.com/repos/yiisoft/yii2/issues/12111/labels{/name}","comments_url":"https://api.github.com/repos/yiisoft/yii2/issues/12111/comments","events_url":"https://api.github.com/repos/yiisoft/yii2/issues/12111/events","html_url":"https://github.com/yiisoft/yii2/issues/12111","id":169910469,"number":12111,"title":"Yii2 Query Builder Issue - Using MySQL user-variables","user":{"login":"edmondetemi","id":12729746,"avatar_url":"https://avatars.githubusercontent.com/u/12729746?v=3","gravatar_id":"","url":"https://api.github.com/users/edmondetemi","html_url":"https://github.com/edmondetemi","followers_url":"https://api.github.com/users/edmondetemi/followers","following_url":"https://api.github.com/users/edmondetemi/following{/other_user}","gists_url":"https://api.github.com/users/edmondetemi/gists{/gist_id}","starred_url":"https://api.github.com/users/edmondetemi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/edmondetemi/subscriptions","organizations_url":"https://api.github.com/users/edmondetemi/orgs","repos_url":"https://api.github.com/users/edmondetemi/repos","events_url":"https://api.github.com/users/edmondetemi/events{/privacy}","received_events_url":"https://api.github.com/users/edmondetemi/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/yiisoft/yii2/labels/feature:db","name":"feature:db","color":"bfd4f2"},{"url":"https://api.github.com/repos/yiisoft/yii2/labels/status:to%20be%20verified","name":"status:to be verified","color":"fbca04"}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":5,"created_at":"2016-08-08T12:24:50Z","updated_at":"2016-08-09T15:27:37Z","closed_at":null,"body":"We have tried to build a query in Yii2 to select 2 articles from each category, but the problem is with the user-variables and with the CASE statement as we see Yii2 doesn't support them in this way, so can anyone please explain or correct us how is the right way to use them in Yii2 in this case.\r\n\r\nThank you in advance.\r\n\r\n------\r\n\r\n```php\r\n$varsQuery = (new \\yii\\db\\Query())\r\n\t\t\t->select(['@var_category_id:=NULL', '@rn:=NULL']);\r\n\t\t\t\r\n$subtableQuery = (new \\yii\\db\\Query())\r\n\t\t\t->select(['category_id', '@rn:=(CASE WHEN @var_category_id = category_id THEN @rn +1 ELSE 1 END) AS rn', '@var_category_id:=category_id'])\r\n\t\t\t->from(['vars' => $varsQuery, 'articles'])\r\n\t\t\t->where(['category_id' => $categories])\r\n\t\t\t->orderBy('published_date ASC');\r\n\t\t\t\r\n$query = (new \\yii\\db\\Query())\r\n\t\t\t->select(['c.id', 'sub_table.*'])\r\n\t\t\t->from(['sub_table' => $subtableQuery])\r\n\t\t\t->innerJoin('categories c', 'c.id = sub_table.category_id')\r\n\t\t\t->where('rn' <= 2)\r\n\t\t\t->orderBy('published_date DESC');\r\n```"},"comment":{"url":"https://api.github.com/repos/yiisoft/yii2/issues/comments/238590077","html_url":"https://github.com/yiisoft/yii2/issues/12111#issuecomment-238590077","issue_url":"https://api.github.com/repos/yiisoft/yii2/issues/12111","id":238590077,"user":{"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},"created_at":"2016-08-09T15:27:36Z","updated_at":"2016-08-09T15:27:36Z","body":"You mean it happens w/ only\r\n\r\n```php\r\n\\Yii::$app->db->createCommand('SET @var = NULL;')->execute();\r\n```\r\n\r\n?"}}
{"actor":{"display_login":"samdark"}}