SqlDataProvider provides data in terms of arrays, each representing a row of query result.
Like other data providers, SqlDataProvider also supports sorting and pagination.
It does so by modifying the given [[sql]] statement with "ORDER BY" and "LIMIT"
clauses. You may configure the [[sort]] and [[pagination]] properties to
customize sorting and pagination behaviors.
SqlDataProvider may be used in the following way:
php
$count = Yii::$app->db->createCommand('
SELECT COUNT(*) FROM user WHERE status=:status
', [':status' => 1])->queryScalar();
$dataProvider = new SqlDataProvider([
'sql' => 'SELECT * FROM user WHERE status=:status',
'params' => [':status' => 1],
'totalCount' => $count,
'sort' => [
'attributes' => [
'age',
'name' => [
'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC],
'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC],
'default' => SORT_DESC,
'label' => 'Name',
],
],
],
'pagination' => [
'pageSize' => 20,
],
]);
get the user records in the current page
$models = $dataProvider->getModels();
Note: if you want to use the pagination feature, you must configure the [[totalCount]] property
to be the total number of rows (without pagination). And if you want to use the sorting feature,
you must configure the [[sort]] property so that the provider knows which columns can be sorted.
For more details and usage information on SqlDataProvider, see the guide article on data providers.
public function dataRekamDiagnosa() { $user = Yii::$app->user->id; $count = Yii::$app->db->createCommand('SELECT COUNT(DISTINCT(diagnosa_ke)) FROM diagnosa_user WHERE user_id=:user_id GROUP BY diagnosa_ke', [':user_id' => $user])->execute(); $dataProvider = new SqlDataProvider(['sql' => 'SELECT * FROM diagnosa_user WHERE user_id=:user_id GROUP BY diagnosa_ke', 'params' => [':user_id' => $user], 'totalCount' => $count, 'sort' => ['attributes' => ['diagnosa_ke']], 'pagination' => ['pageSize' => 20]]); // get the user records in the current page $models = $dataProvider->getModels(); return $dataProvider; }
public function dataRekamDiagnosa($ke) { $user = Yii::$app->user->id; // $count = Yii::$app->db->createCommand('SELECT count(id) FROM diagnosa_user_detil', [':user_id' => $user])->execute(); $count = Yii::$app->db->createCommand('SELECT count(id) FROM diagnosa_user_detil')->execute(); $sql = "SELECT \n dud.`diagnosa_user_id`,\n du.`user_id`,\n du.`diagnosa_ke`,\n du.`diagnosa_date`,\n dud.`gejala_id`,\n pg.`nama_gejala`\n FROM diagnosa_user_detil dud \n JOIN penyakit_gejala pg ON dud.`gejala_id`=pg.`id`\n JOIN diagnosa_user du ON du.`id`=dud.`diagnosa_user_id`\n where du.`user_id`=:user_id and du.`diagnosa_ke`=:ke\n #ORDER BY du.`id`,dud.`gejala_id`"; $dataProvider = new SqlDataProvider(['sql' => $sql, 'params' => [':user_id' => $user, ':ke' => $ke], 'totalCount' => $count, 'sort' => ['attributes' => ['diagnosa_ke', 'nama_gejala']], 'pagination' => ['pageSize' => 5]]); // get the user records in the current page $models = $dataProvider->getModels(); return $dataProvider; }
public function actionProposalExport() { // return 'Export excel'; $dataProvider = new SqlDataProvider(['sql' => "SELECT * FROM proposal"]); $filename = "Proposal-Export-" . Date('YmdGis') . '-Mahasiswa.xls'; $model = $dataProvider->getModels(); header('Content-type:application/vnd-ms-excel'); header('Content-Disposition: attachement;filename=' . $filename); echo "<h1>Proposal Mahasiswa</h1>"; echo "<table border=1>\n <tr>\n <td>No</td>\n <td>Nim</td>\n <td>Nama Mahasiswa</td>\n <td>Judul Proposal</td>\n <td>Pembimbing</td>\n </tr>"; $no = 1; foreach ($model as $key => $value) { echo "\n <tr>\n <td>" . $no . "</td>\n <td>" . $value['nim'] . "</td>\n <td>" . $value['nama_mahasiswa'] . "</td>\n <td>" . $value['judul'] . "</td>\n <td>" . $value['nama_dosen'] . "</td>\n </tr>"; $no++; } echo "</table>"; }
public static function getAllActivityByUserId($userId = '', $isPage = true) { $count = Yii::$app->db->createCommand('SELECT (au.id) as count FROM ' . self::tableName() . ' au LEFT JOIN ' . Activity::tableName() . ' a ON au.activity_id = a.id WHERE au.user_id=:user_id', [':user_id' => $userId])->queryScalar(); $dataProvider = new SqlDataProvider(['sql' => 'SELECT au.id as id,au.activity_id,a.name,a.category,a.create_time,a.address,a.lesson,a.score,au.status,a.begin_time,a.recruit_count FROM ' . self::tableName() . ' au LEFT JOIN ' . Activity::tableName() . ' a ON au.activity_id = a.id WHERE au.user_id=:user_id', 'params' => [':user_id' => $userId], 'totalCount' => $count, 'pagination' => ['pageSize' => $isPage ? '5' : '9999']]); $models = $dataProvider->getModels(); return $models; }
/** * Show advanced options for a request model. * For ajax request will return json object * and for non-ajax request if deletion is successful, the browser will be redirected to the 'index' page. * @param string $id * @return mixed * @throws NotFoundHttpException */ public function actionAdvanced($id) { $request = Yii::$app->request; if (!Yii::$app->user->isGuest) { $specificRequest = $this->findModel($id); if ($request->isAjax) { /* * Process for ajax request */ Yii::$app->response->format = Response::FORMAT_JSON; return ['forceClose' => true, 'forceReload' => '#crud-datatable-pjax']; } else { /* * Process for non-ajax request */ $availableUsersQuery = new Query(); $availableUsersQuery->select('*')->from('users')->leftJoin('users_request', '`users`.`id` = `users_request`.`user_id` and `users_request`.`request_id` = ' . $id)->where(['users_request.user_id' => null]); $command = $availableUsersQuery->createCommand(); $availableUsers = $command->queryAll(); $users = ArrayHelper::map($availableUsers, 'id', 'first_name'); $dataProvider = new SqlDataProvider(['sql' => 'SELECT * FROM users_request WHERE request_id=:id', 'params' => [':id' => $id]]); $models = $dataProvider->getModels(); return $this->render('advanced', ['request' => $specificRequest, 'users' => $users, 'responsible' => $models]); } } else { throw new NotFoundHttpException('The requested page does not exist.'); } }
public function actionVolunteer($id) { $count = Yii::$app->db->createCommand("SELECT COUNT(EV.id) FROM edge_volunteer EV INNER JOIN volunteers V ON V.id = EV.reference_id WHERE 1=1 AND course_id=:course_id", [":course_id" => $id])->queryScalar(); $dataProvider = new SqlDataProvider(['sql' => 'SELECT EV.id,EV.course_id,V.first_name,V.last_name FROM edge_volunteer EV INNER JOIN volunteers V ON V.id = EV.reference_id WHERE 1=1 AND course_id=:course_id', 'params' => [':course_id' => $id], 'totalCount' => $count, 'sort' => ['attributes' => ['name' => ['asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC], 'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC], 'default' => SORT_DESC, 'label' => 'Name'], 'first_name', 'last_name']], 'pagination' => ['pageSize' => 20]]); $models = $dataProvider->getModels(); $model = EdgeCourse::findOne($id); return $this->render('volunteer', ["model" => $dataProvider, "modelData" => $model]); }
public function actionSearch($type, $reg, $w, $p, $n, $m) { $conn = \Yii::$app->db; $regionalsN = $conn->createCommand('SELECT UCS from NEW_SME where UCS is not null group by UCS order by UCS ASC')->query(); $productsN = $conn->createCommand("SELECT PROD_TELKOM FROM NEW_SME WHERE PROD_TELKOM IS NOT NULL OR PROD_TELKOM!='' GROUP BY PROD_TELKOM ORDER BY PROD_TELKOM ASC")->query(); $nonproductsN = $conn->createCommand("SELECT NON_PRODUK_TELKOM FROM NEW_SME WHERE NON_PRODUK_TELKOM IS NOT NULL OR NON_PRODUK_TELKOM!='' GROUP BY NON_PRODUK_TELKOM ORDER BY NON_PRODUK_TELKOM ASC")->query(); $regionalsE = $conn->createCommand('SELECT REGIONAL from DATA_PROSPEK where REGIONAL is not null group by REGIONAL order by REGIONAL ASC')->query(); $productsE = $conn->createCommand("SELECT P_TYPE FROM DATA_PROSPEK WHERE P_TYPE IS NOT NULL OR P_TYPE!='' GROUP BY P_TYPE ORDER BY P_TYPE ASC")->query(); $networksE = $conn->createCommand("SELECT TYPE_JARINGAN FROM DATA_PROSPEK WHERE TYPE_JARINGAN IS NOT NULL OR TYPE_JARINGAN!='' GROUP BY TYPE_JARINGAN ORDER BY TYPE_JARINGAN ASC")->query(); if ($type == 'new') { if ($m == 'twitter') { $sql = "(TWITTER_ACCOUNT IS NOT NULL OR TWITTER_ACCOUNT!='')"; } else { $sql = "(FACEBOOK_ACCOUNT IS NOT NULL OR FACEBOOK_ACCOUNT!='')"; } if (!empty($reg)) { $sql .= " AND UCS='{$reg}'"; } if (!empty($w)) { $sql .= " AND WITEL='{$w}'"; } if (!empty($p)) { $sql .= " AND PROD_TELKOM='{$p}'"; } if (!empty($n)) { $sql .= " AND NON_PRODUK_TELKOM='{$n}'"; } $count = $conn->createCommand("SELECT COUNT(*) FROM NEW_SME WHERE {$sql}")->queryScalar(); $query = "SELECT * FROM NEW_SME WHERE {$sql}"; $sort = ['attributes' => ['NAMA_PERUSAHAAN' => ['asc' => ['NAMA_PERUSAHAAN' => 'SORT_ASC'], 'desc' => ['NAMA_PERUSAHAAN' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'Nama Perusahaan'], 'ALAMAT' => ['asc' => ['ALAMAT' => 'SORT_ASC'], 'desc' => ['ALAMAT' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'Alamat'], 'KOTA' => ['asc' => ['KOTA' => 'SORT_ASC'], 'desc' => ['KOTA' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'Kota']]]; } elseif ($type == 'exist') { if ($m == 'twitter') { $sql = "(TWITTER_ACCOUNT IS NOT NULL OR TWITTER_ACCOUNT!='')"; } else { $sql = "(FACEBOOK_ACCOUNT IS NOT NULL OR FACEBOOK_ACCOUNT!='')"; } if (!empty($reg)) { $sql = "REGIONAL='{$reg}'"; } if (!empty($w)) { $sql .= " AND WITEL='{$w}'"; } if (!empty($p)) { $sql .= " AND P_TYPE='{$p}'"; } if (!empty($n)) { $sql .= " AND TYPE_JARINGAN='{$n}'"; } $count = $conn->createCommand("SELECT COUNT(*) FROM DATA_PROSPEK WHERE {$sql}")->queryScalar(); $query = "SELECT * FROM DATA_PROSPEK WHERE {$sql}"; $sort = ['attributes' => ['NIPNAS_GROUP' => ['asc' => ['NIPNAS_GROUP' => 'SORT_ASC'], 'desc' => ['NIPNAS_GROUP' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'NIPNAS Group'], 'CLIENT_NAME' => ['asc' => ['CLIENT_NAME' => 'SORT_ASC'], 'desc' => ['CLIENT_NAME' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'Nama Client'], 'CITY' => ['asc' => ['CITY' => 'SORT_ASC'], 'desc' => ['CITY' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'Kota']]]; } else { throw new NotFoundHttpException('The requested page does not exist.'); } $dataProvider = new SqlDataProvider(['sql' => $query, 'totalCount' => (int) $count, 'sort' => $sort, 'pagination' => ['pageSize' => 20]]); $models = []; foreach ($dataProvider->getModels() as $mm) { $models[] = [$mm['ID'], $mm['TWITTER_ACCOUNT']]; } Yii::$app->session->set('models', $models); if ($type == 'new') { $p = $_GET['p'] !== '' ? $_GET['p'] : 'All Produk'; $n = $_GET['n'] !== '' ? $_GET['n'] : 'All Non-Produk'; } elseif ($type == 'exist') { $p = $_GET['p'] !== '' ? $_GET['p'] : 'All Prospek'; $n = $_GET['n'] !== '' ? $_GET['n'] : 'All Tipe Jaringan'; } return $this->render('search', ['type' => $type, 'reg' => $_GET['reg'] !== '' ? $_GET['reg'] : 'All Regional', 'w' => $_GET['w'] !== '' ? $_GET['w'] : 'All Witel', 'p' => $p, 'n' => $n, 'm' => $_GET['m'], 'dataProvider' => $dataProvider, 'models' => $models, 'regionalsN' => $regionalsN, 'productsN' => $productsN, 'nonproductsN' => $nonproductsN, 'regionalsE' => $regionalsE, 'productsE' => $productsE, 'networksE' => $networksE]); }
public static function getAllTrainByUserId($userId = '', $isPage = true) { $count = Yii::$app->db->createCommand('SELECT (tu.id) as count FROM ' . self::tableName() . ' tu LEFT JOIN ' . Train::tableName() . ' t ON tu.train_id = t.id WHERE tu.user_id=:user_id ', [':user_id' => $userId])->queryScalar(); $dataProvider = new SqlDataProvider(['sql' => 'SELECT tu.id as id,tu.train_id,t.name,t.category,t.create_time,t.address,t.recruit_count,tu.status,t.begin_time,tu.user_id as train_user_id,t.period_num,t.train_land_id FROM ' . TrainUsers::tableName() . ' tu LEFT JOIN ' . Train::tableName() . ' t ON tu.train_id = t.id WHERE tu.user_id=:user_id ORDER BY id desc', 'params' => [':user_id' => $userId], 'totalCount' => $count, 'pagination' => ['pageSize' => $isPage ? '5' : '9999']]); $models = $dataProvider->getModels(); return $models; }
/** * @param boolean $model Whether to return models (true, default) or arrays * @return ArrayDataProvider */ private function generateProvider($model = true) { if ($model) { return new ArrayDataProvider(['allModels' => [new ModelRow(['x' => 0, 'y1' => 2, 'y2' => 10, 'y3' => -5]), new ModelRow(['x' => 1, 'y1' => 4, 'y2' => 6, 'y3' => -6]), new ModelRow(['x' => 2, 'y1' => 6, 'y2' => 2, 'y3' => -7])]]); } else { $mockProvider = $this->getMockBuilder(SqlDataProvider::className())->getMock(); $mockProvider->method("getModels")->willReturn([['x' => 0, 'y1' => 2, 'y2' => 10, 'y3' => -5], ['x' => 1, 'y1' => 4, 'y2' => 6, 'y3' => -6], ['x' => 2, 'y1' => 6, 'y2' => 2, 'y3' => -7]]); return $mockProvider; } }
/** * Получение id специалистов, у который есть заявки на приём на заданный день $date_ * @param date $date_ дата приёма * @return mixed */ public function actionGetDayReportMain($date_) { $sql = 'SELECT `rd`.`specialist_id`, `oc`.`name` AS `oname`, `dr`.`name` AS `dname`, `rd`.`start_time` FROM `records` AS `rd`, `occupations` AS `oc`, `specialists` AS `sp`, `doctors` AS `dr` WHERE DATE(`rd`.`start_time`) = "' . $date_ . '" AND `rd`.`reserved` = "1" AND `rd`.`visited` = "0" AND `rd`.`specialist_id` = `sp`.`id` AND `sp`.`occupation_id` = `oc`.`id` AND `sp`.`doctor_id` = `dr`.`id` GROUP BY `specialist_id` ORDER BY `dname`'; $provider = new SqlDataProvider(['sql' => $sql]); $models = $provider->getModels(); return Json::encode($models); }