{"action":"created","issue":{"url":"https://api.github.com/repos/yiisoft/yii2/issues/8340","labels_url":"https://api.github.com/repos/yiisoft/yii2/issues/8340/labels{/name}","comments_url":"https://api.github.com/repos/yiisoft/yii2/issues/8340/comments","events_url":"https://api.github.com/repos/yiisoft/yii2/issues/8340/events","html_url":"https://github.com/yiisoft/yii2/issues/8340","id":74852529,"number":8340,"title":"Not correct work of DataProvider","user":{"login":"Alex-Bond","id":1134715,"avatar_url":"https://avatars.githubusercontent.com/u/1134715?v=3","gravatar_id":"","url":"https://api.github.com/users/Alex-Bond","html_url":"https://github.com/Alex-Bond","followers_url":"https://api.github.com/users/Alex-Bond/followers","following_url":"https://api.github.com/users/Alex-Bond/following{/other_user}","gists_url":"https://api.github.com/users/Alex-Bond/gists{/gist_id}","starred_url":"https://api.github.com/users/Alex-Bond/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Alex-Bond/subscriptions","organizations_url":"https://api.github.com/users/Alex-Bond/orgs","repos_url":"https://api.github.com/users/Alex-Bond/repos","events_url":"https://api.github.com/users/Alex-Bond/events{/privacy}","received_events_url":"https://api.github.com/users/Alex-Bond/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-05-10T06:47:12Z","updated_at":"2015-05-13T10:57:58Z","closed_at":"2015-05-10T13:24:03Z","body":"Hi!\r\n\r\nI have strange problem with DataProvider.\r\n\r\nThis is function from controller:\r\n```php\r\n public function actionGetProducts()\r\n    {\r\n        $columns      = [\r\n            1 => 'id',\r\n            2 => 'name',\r\n            3 => 'sku',\r\n            4 => 'category',\r\n            5 => 'price',\r\n            6 => 'quantity',\r\n            7 => 'status'\r\n        ];\r\n        $query        = Products::find();\r\n        $dataProvider = new ActiveDataProvider([\r\n            'query' => $query,\r\n            //'pagination' => false\r\n        ]);\r\n        $dataProvider->pagination->setPage($_POST['page'] == 1 ? 0 : $_POST['page']);\r\n        $dataProvider->pagination->setPageSize($_POST['pageSize']);\r\n        //$dataProvider->pagination->limit = $_POST['length'];\r\n        //$query->limit($_POST['length'])->offset($_POST['start']);\r\n        $dataProvider->setSort([\r\n            'defaultOrder' => 'id ASC'\r\n        ]);\r\n\r\n        $query->addOrderBy([\r\n            $columns[$_POST['order'][0]['column']] => $_POST['order'][0]['dir'] == 'asc' ? SORT_ASC : SORT_DESC\r\n        ]);\r\n\r\n        $query->joinWith([ 'categories', 'categories.category' ]);\r\n        if (isset( $_POST['Products'] )) {\r\n            foreach ($_POST['Products'] as $key => $value) {\r\n                switch ($key) {\r\n                    case 'ignoreId':\r\n                        $query->andWhere([ 'NOT IN', 'products.id', $value ]);\r\n                        break;\r\n                    case \"category\":\r\n                        if (!empty( $value )) {\r\n                            $query->andWhere([ 'productCategories.category_id' => $value ]);\r\n                        }\r\n                        break;\r\n                    case \"price\":\r\n                    case \"quantity\":\r\n                        if (!empty( $value['from'] ) && !empty( $value['to'] )) {\r\n                            $query->andWhere([ 'between', 'products.' . $key, $value['from'], $value['to'] ]);\r\n                        }\r\n                        break;\r\n                    default:\r\n                        if ($value != '') {\r\n                            $query->andWhere([ 'products.' . $key => $value ]);\r\n                        }\r\n                        break;\r\n                }\r\n            }\r\n        }\r\n\r\n        return $this->renderPartial('productsJson', [ 'dataProvider' => $dataProvider ]);\r\n    }\r\n```\r\n\r\nI have 111 products in DB. Every product can have multiple categories. When i requesting first page with 150 limit it returns only 75 records.\r\n\r\nWhen i requesting builded query via console it returns copies of same item with different categories.\r\n\r\nHow to fix it?"},"comment":{"url":"https://api.github.com/repos/yiisoft/yii2/issues/comments/101626510","html_url":"https://github.com/yiisoft/yii2/issues/8340#issuecomment-101626510","issue_url":"https://api.github.com/repos/yiisoft/yii2/issues/8340","id":101626510,"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-05-13T10:57:58Z","updated_at":"2015-05-13T10:57:58Z","body":"no, the problem in your case is that your SQL returns some of the records in duplicated way and also may drop some from the result because of the join. AR will group by primary key automatically so duplicate records are filtered out. You have to adjust your query to ensure you only get a record once."}}