PHP Query::count方法代码示例

 public function actionRegion($id = null)
 {
     if ($id) {
         $model = Geo::getOne($id);
         //получчаем количество розничных точек в регионе
         $col_apteki = Apteki::find()->where(['region_id' => $id])->count();
         //получаем количество Юр.лиц
         $col_ur_l = RegionUrL::find()->where(['id_reg' => $id])->count();
         $db = new Query();
         $db->from('region_ur_l');
         $db->InnerJoin('ur_l', 'id_ur = ur_l.id');
         $db->andWhere(['=', 'id_reg', $id]);
         $db->andWhere(['=', 'ur_l.regional_id', $model['regional_id']]);
         $col_ur_l_regpred = $db->count();
         //  $col_ur_l_regpred = RegionUrL::find()->where(['id_reg' => $id])->andWhere(['$model'=>])->count();
     } else {
         $model = Geo::getAll();
     }
     $region = new Geo();
     if ($_POST['Geo']) {
         $model->attributes = $_POST['Geo'];
         if ($model->validate() && $model->save()) {
             return $this->render('/edit/region', ['model' => $model, 'region' => $region, 'ok' => 1, 'col_apteki' => $col_apteki, 'col_ur_l' => $col_ur_l, 'col_ur_l_regpred' => $col_ur_l_regpred]);
         }
     }
     if ($id) {
         return $this->render('/edit/region', ['model' => $model, 'col_apteki' => $col_apteki, 'col_ur_l' => $col_ur_l, 'col_ur_l_regpred' => $col_ur_l_regpred]);
     } else {
         return $this->render('region', ['model' => $model]);
     }
 }

 /**
  * @inheritDoc
  */
 public function actionDown($limit = 'all')
 {
     $ret = parent::actionDown('all');
     $query = new Query();
     $query->from($this->migrationTable);
     if (1 == $query->count()) {
         $this->deleteMigrationHistoryTable();
     }
     return $ret;
 }

 public function getOne($mat_id, $num_tour)
 {
     $query = new Query();
     $query->select('id, mat_id, numero_tour, done')->from('kanban')->where('mat_id = ' . $mat_id . ' AND numero_tour = ' . $num_tour);
     if ($query->count() > 0) {
         $data = $query->all();
         return $this->arrayToKanbanList($data)[0];
     } else {
         return null;
     }
 }

 /**
  * Lists all ExploreRecommend models.
  * @return mixed
  */
 public function actionIndex()
 {
     $query = new Query();
     $query->select('id, summary, created_at, title, username, user_id')->from('{{%explore_recommend}}')->where(['category' => 'post'])->orderBy('id DESC');
     $pages = new \yii\data\Pagination(['totalCount' => $query->count()]);
     $posts = $query->offset($pages->offset)->limit($pages->limit)->all();
     $forums = new Query();
     $forums = $forums->select('f.forum_name, f.forum_url, f.forum_desc, f.forum_icon, e.id')->from('{{%forum}} as f')->join('LEFT JOIN', '{{%explore_recommend}} as e', 'e.table_id=f.id')->where(['e.category' => 'forum'])->orderBy('e.id DESC')->all();
     $albums = new Query();
     $albums = $albums->select('id, table_id')->from('{{%explore_recommend}}')->where(['category' => 'album'])->orderBy('id DESC')->all();
     return $this->render('index', ['posts' => $posts, 'pages' => $pages, 'forums' => $forums, 'albums' => $albums]);
 }

 /**
  * @inheritDoc
  */
 public function actionDown($limit = 'all')
 {
     $ret = parent::actionDown('all');
     $query = new Query();
     $query->from($this->migrationTable);
     if (1 == $query->count()) {
         $tableName = $this->db->schema->getRawTableName($this->migrationTable);
         $this->stdout("Deleting migration history table \"{$tableName}\"...", Console::FG_YELLOW);
         $this->db->createCommand()->dropTable($this->migrationTable)->execute();
         $this->stdout("Done.\n", Console::FG_GREEN);
     }
     return $ret;
 }

 /**
  * Lists all City models.
  * @return mixed
  */
 public function actionIndex()
 {
     $params = $_REQUEST;
     $filter = array();
     $sort = "";
     $page = 1;
     $limit = 10;
     if (isset($params['page'])) {
         $page = $params['page'];
     }
     if (isset($params['limit'])) {
         $limit = $params['limit'];
     }
     $offset = $limit * ($page - 1);
     /* Filter elements */
     if (isset($params['filter'])) {
         $filter = (array) json_decode($params['filter']);
     }
     if (isset($params['datefilter'])) {
         $datefilter = (array) json_decode($params['datefilter']);
     }
     if (isset($params['sort'])) {
         $sort = $params['sort'];
         if (isset($params['order'])) {
             if ($params['order'] == "false") {
                 $sort .= " desc";
             } else {
                 $sort .= " asc";
             }
         }
     }
     $query = new Query();
     $query->offset($offset)->limit($limit)->from('user')->andFilterWhere(['like', 'id', $filter['id']])->andFilterWhere(['like', 'name', $filter['name']])->andFilterWhere(['like', 'age', $filter['age']])->orderBy($sort)->select("id,name,age,createdAt,updatedAt");
     if ($datefilter['from']) {
         $query->andWhere("createdAt >= '" . $datefilter['from'] . "' ");
     }
     if ($datefilter['to']) {
         $query->andWhere("createdAt <= '" . $datefilter['to'] . "'");
     }
     $command = $query->createCommand();
     $models = $command->queryAll();
     $totalItems = $query->count();
     $this->setHeader(200);
     echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT);
 }

 /**
  * Lists all Airports models.
  * @return mixed
  */
 public function actionSearch()
 {
     $params = Yii::$app->request->queryParams;
     $filter = array();
     $sort = "";
     $page = 1;
     $limit = 10;
     if (isset($params['page'])) {
         $page = $params['page'];
     }
     if (isset($params['limit'])) {
         $limit = $params['limit'];
     }
     $offset = $limit * ($page - 1);
     /* Filter elements */
     if (isset($params['filter'])) {
         $filter = (array) json_decode($params['filter']);
     }
     if (isset($params['datefilter'])) {
         $datefilter = (array) json_decode($params['datefilter']);
     }
     if (isset($params['sort'])) {
         $sort = $params['sort'];
         if (isset($params['order'])) {
             if ($params['order'] == "false") {
                 $sort .= " desc";
             } else {
                 $sort .= " asc";
             }
         }
     }
     $query = new Query();
     if (!empty($params)) {
         $query->offset($offset)->limit($limit)->from('airports')->andFilterWhere(['like', 'id', $filter['id']])->andFilterWhere(['like', 'airport_code', $filter['airport_code']])->andFilterWhere(['like', 'airport_name', $filter['airport_name']])->andFilterWhere(['like', 'country', $filter['country']])->andFilterWhere(['like', 'city', $filter['city']])->orderBy($sort)->select("id, airport_code, airport_name, country, city");
     } else {
         $query->offset($offset)->limit($limit)->from('airports')->orderBy($sort)->select("id, airport_code, airport_name, country, city");
     }
     $command = $query->createCommand();
     $models = $command->queryAll();
     $totalItems = $query->count();
     $this->setHeader(200);
     echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT);
 }

 public static function queryDatas($tableName, $conditions, $offset, $size, $orderby, $isGetTotal = false)
 {
     $query = new Query();
     $query->select('*')->from($tableName);
     if (!empty($conditions)) {
         foreach ($conditions as $name => $value) {
             if (is_array($value)) {
                 $query->andWhere($value);
             } else {
                 $query->andWhere([$name => $value]);
             }
         }
     }
     $total = 0;
     if ($isGetTotal) {
         $total = $query->count('*');
     }
     $query->offset($offset)->limit($size)->orderBy($orderby);
     $list = $query->all();
     return ['list' => $list, 'total' => $total];
 }

 protected function getCountByCommand($tableName, Query $command)
 {
     $command->from($tableName);
     return (int) $command->count();
 }

 /**
  * Updates all Entries of a Contest with votecount and avg rating
  * 
  * @param \app\models\Contest $Contest Contest Object
  */
 private function UpdateContestEntries($Contest)
 {
     $Entries = Entry::findAll(['contest_id' => $Contest->id]);
     foreach ($Entries as $Entry) {
         $avgQuery = new Query();
         $avgQuery->from(Tweet::tableName())->where(['entry_id' => $Entry->id, 'needs_validation' => 0, 'old_vote' => 0]);
         $avgRating = $avgQuery->average('rating');
         $votestQuery = new Query();
         $votestQuery->from(Tweet::tableName())->where(['entry_id' => $Entry->id, 'needs_validation' => 0, 'old_vote' => 0]);
         $votes = $votestQuery->count();
         $Entry->votes = $votes;
         $Entry->avg_rating = round($avgRating, 2);
         $Entry->save();
     }
 }

 public function actionUsers()
 {
     $_currUser = Yii::$app->user->isGuest ? null : User::findOne(['id' => Yii::$app->user->id]);
     if (!$_currUser || 0 === $_currUser->is_admin) {
         return $this->goHome();
     }
     if ($_currUser && $_currUser->is_admin && Yii::$app->request->get('user_id') && 'markAdmin' === Yii::$app->request->get('action')) {
         $tarUser = User::findOne(['id' => intval(Yii::$app->request->get('user_id'))]);
         if ($tarUser) {
             $tarUser->is_admin = 1;
             $tarUser->save();
         }
     }
     $users = [];
     $currPage = Yii::$app->request->get('page', 0);
     $filteredAddress = Yii::$app->request->get('address', null);
     $queryUserCount = new Query();
     $queryUserCount->select('*')->from('users u');
     $queryUser = new Query();
     $queryUser->select('u.id as id, u.first_name as fname, u.last_name as lname, u.is_admin as is_admin, ' . 'u.photo as photo, u.is_complete as is_complete, avg(r.rate) as rate')->from('users u')->leftJoin('rates r', 'r.rated_id = u.id')->groupBy('id, fname, lname, is_admin, photo, is_complete')->orderBy('rate desc')->offset(intval(Yii::$app->params['driversPerPageLimit'] * $currPage))->limit(Yii::$app->params['driversPerPageLimit']);
     if ($filteredAddress) {
         $queryUser->leftJoin('rel_user_address rua', 'rua.user_id = u.id');
         $queryUser->leftJoin('addresses adr', 'adr.id = rua.address_id');
         $queryUser->where('(u.user_type = :utype) AND (adr.address LIKE :address)', [':utype' => User::$_TYPE_CUSTOMER, ':address' => "%{$filteredAddress}%"]);
         $queryUserCount->leftJoin('rel_user_address rua', 'rua.user_id = u.id');
         $queryUserCount->leftJoin('addresses adr', 'adr.id = rua.address_id');
         $queryUserCount->where('(u.user_type = :utype) AND (adr.address LIKE :address)', [':utype' => User::$_TYPE_CUSTOMER, ':address' => "%{$filteredAddress}%"]);
     } else {
         $queryUser->where('u.user_type = :utype', [':utype' => User::$_TYPE_CUSTOMER]);
         $queryUserCount->where('u.user_type = :utype', [':utype' => User::$_TYPE_CUSTOMER]);
     }
     $totalCount = $queryUserCount->count();
     $context = ['is_guest' => Yii::$app->user->isGuest, 'curr_user_id' => Yii::$app->user->isGuest ? null : Yii::$app->user->id, 'is_admin' => $_currUser ? 1 === $_currUser->is_admin : false, 'is_drivers' => 0, 'total_pages' => ceil($totalCount / Yii::$app->params['driversPerPageLimit']), 'total_count' => $totalCount, 'current_page' => $currPage, 'filtered_address' => $filteredAddress, 'address_start' => Yii::$app->request->get('address_start', null), 'address_dest' => Yii::$app->request->get('address_dest', null), 'time_start' => Yii::$app->request->get('time_start', null), 'message' => Yii::$app->request->get('message', null)];
     $rows = $queryUser->all();
     foreach ($rows as $row) {
         $users[] = ['id' => $row['id'], 'first_name' => $row['fname'], 'last_name' => $row['lname'], 'is_admin' => $row['is_admin'], 'rating' => sprintf("%.2f", $row['rate']), 'photo' => $row['photo'], 'is_complete' => $row['is_complete']];
     }
     return $this->render('drivers', ['drivers' => $users, 'context' => $context]);
 }

 public function actionIndex()
 {
     $params = $_REQUEST;
     //TODO Calling unknown method: yii\web\Request::getParams() why?
     //$params = \Yii::$app->request->getParams();
     var_dump($params);
     exit;
     $filter = array();
     $sort = "";
     $page = 1;
     $limit = 10;
     if (isset($params['page'])) {
         $page = $params['page'];
     }
     if (isset($params['limit'])) {
         $limit = $params['limit'];
     }
     $offset = $limit * ($page - 1);
     /* Filter elements */
     if (isset($params['filter'])) {
         $filter = (array) json_decode($params['filter']);
     }
     if (isset($params['datefilter'])) {
         $datefilter = (array) json_decode($params['datefilter']);
     }
     if (isset($params['sort'])) {
         $sort = $params['sort'];
         if (isset($params['order'])) {
             if ($params['order'] == "false") {
                 $sort .= " desc";
             } else {
                 $sort .= " asc";
             }
         }
     }
     $query = new Query();
     $query->offset($offset)->limit($limit)->from('user')->orderBy($sort)->select("*");
     /*if ($datefilter['from']) {
           $query->andWhere("createdAt >= '" . $datefilter['from'] . "' ");
       }
       if ($datefilter['to']) {
           $query->andWhere("createdAt <= '" . $datefilter['to'] . "'");
       }*/
     $command = $query->createCommand();
     $models = $command->queryAll();
     $totalItems = $query->count();
     $this->setHeader(200);
     echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT);
 }

 /**
  * Dashboard
  *
  * @return mixed
  */
 public function actionIndex()
 {
     // Unique users today
     $usersQuery = new Query();
     $usersQuery->select(['domain_userid'])->from('{{%event}}')->where('DATE(FROM_UNIXTIME(collector_tstamp)) = CURRENT_DATE')->distinct();
     // Unique users total
     $totalUsersQuery = new Query();
     $totalUsersQuery->select(['users'])->from('{{%stats_performance}}')->where('DATE(day) != CURRENT_DATE');
     // Submissions today
     $submissionsQuery = new Query();
     $submissionsQuery->select(['id'])->from('{{%form_submission}}')->andWhere('DATE(FROM_UNIXTIME(created_at)) = CURRENT_DATE');
     // Submissions total
     $totalSubmissionsQuery = new Query();
     $totalSubmissionsQuery->select(['id'])->from('{{%form_submission}}');
     // If not admin
     if (!empty(Yii::$app->user) && !Yii::$app->user->can("admin")) {
         // Add user filter to queries
         $formIds = Yii::$app->user->getAssignedFormIds();
         $formIds = count($formIds) > 0 ? $formIds : 0;
         // Important restriction
         $usersQuery->andFilterWhere(['app_id' => $formIds]);
         $totalUsersQuery->andFilterWhere(['app_id' => $formIds]);
         $submissionsQuery->andFilterWhere(['form_id' => $formIds]);
         $totalSubmissionsQuery->andFilterWhere(['form_id' => $formIds]);
     }
     // Execute queries
     $users = $usersQuery->count();
     $totalUsers = $totalUsersQuery->sum('users');
     $submissions = $submissionsQuery->count();
     $totalSubmissions = $totalSubmissionsQuery->count();
     // Add today data to total
     $totalUsers = $totalUsers + $users;
     // Users / submissions = Conversion rate
     $submissionRate = 0;
     if ($users > 0 && $submissions > 0) {
         $submissionRate = round($submissions / $users * 100);
     }
     $totalSubmissionRate = 0;
     if ($totalUsers > 0 && $totalSubmissions > 0) {
         $totalSubmissionRate = round($totalSubmissions / $totalUsers * 100);
     }
     // Most viewed forms list by unique users
     $formsByUsersQuery = (new Query())->select(['f.id', 'f.name', 'COUNT(DISTINCT(e.domain_userid)) AS users'])->from('{{%event}} AS e')->innerJoin('{{%form}} AS f', 'e.app_id = f.id')->where(['event' => 'pv'])->andWhere('DATE(FROM_UNIXTIME(collector_tstamp)) = CURRENT_DATE')->groupBy(['id'])->orderBy('users DESC')->limit(Yii::$app->params['ListGroup.listSize']);
     // Forms list by submissions
     $formsBySubmissionsQuery = (new Query())->select(['f.id', 'f.name', 'COUNT(fs.id) AS submissions'])->from('{{%form_submission}} AS fs')->innerJoin('{{%form}} as f', 'fs.form_id = f.id')->where('DATE(FROM_UNIXTIME(fs.created_at)) = CURRENT_DATE')->groupBy(['f.id'])->orderBy('submissions DESC')->limit(Yii::$app->params['ListGroup.listSize']);
     // Last updated forms list
     $lastUpdatedFormsQuery = (new Query())->select(['id', 'name', 'updated_at'])->from('{{%form}} AS f')->where('DATE(FROM_UNIXTIME(updated_at)) = CURRENT_DATE')->orderBy('updated_at DESC')->limit(Yii::$app->params['ListGroup.listSize']);
     // If not admin
     if (!empty(Yii::$app->user) && !Yii::$app->user->can("admin")) {
         // Add user filter to queries
         $formIds = Yii::$app->user->getAssignedFormIds();
         $formIds = count($formIds) > 0 ? $formIds : 0;
         // Important restriction
         $formsByUsersQuery->andFilterWhere(['f.id' => $formIds]);
         $formsBySubmissionsQuery->andFilterWhere(['fs.form_id' => $formIds]);
         $lastUpdatedFormsQuery->andFilterWhere(['f.id' => $formIds]);
     }
     $formsByUsers = $formsByUsersQuery->all();
     $formsBySubmissions = $formsBySubmissionsQuery->all();
     $lastUpdatedForms = $lastUpdatedFormsQuery->all();
     return $this->render('index', ['users' => $users, 'submissions' => $submissions, 'submissionRate' => $submissionRate, 'totalUsers' => $totalUsers, 'totalSubmissions' => $totalSubmissions, 'totalSubmissionRate' => $totalSubmissionRate, 'formsByUsers' => $formsByUsers, 'formsBySubmissions' => $formsBySubmissions, 'lastUpdatedForms' => $lastUpdatedForms]);
 }

 public function actionIndex()
 {
     Yii::$app->response->format = Response::FORMAT_JSON;
     $params = Yii::$app->getRequest();
     $filter = array();
     $sort = "";
     $page = isset($params->page) ? $params->page : 1;
     $limit = isset($params->limit) ? $params->limit : 10;
     $offset = $limit * ($page - 1);
     /* Filter elements */
     if (isset($params->filter)) {
         $filter = (array) json_decode($params->filter);
     }
     if (isset($params->sort)) {
         $sort = $params->sort;
         if (isset($params->order)) {
             if ($params->order == "false") {
                 $sort .= " desc";
             } else {
                 $sort .= " asc";
             }
         }
     }
     $query = new Query();
     $query->offset($offset)->limit($limit)->from('restaurants')->orderBy($sort)->select("restaurant_id, name, street, house_nr, flat_nr, zip_code, city");
     $command = $query->createCommand();
     $models = $command->queryAll();
     $totalItems = $query->count();
     echo json_encode(array('status' => 1, 'code' => 200, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT);
 }

 protected function resetRank()
 {
     $author = YBoardMember::findOne($this->user_id);
     if ($author !== null) {
         $query = new Query();
         $query->select('id')->from('YBoardRank')->orderBy('min_posts ASC')->where('min_posts>=' . $author->totalReplies)->limit(1);
         $count = $query->count();
         $id = $query->one();
         if ($author->rank_id != $id['id'] && $count > 0) {
             $author->setAttribute('rank_id', $id['id']);
             $author->save();
         }
     }
 }