PHP Query::select方法代码示例

 /**
  * Lists all Articlecomment models.
  * @return mixed
  */
 public function actionIndex()
 {
     $list = '';
     $post = Yii::$app->request->post();
     if ($post['id']) {
         $mod = new Query();
         $comment = $mod->select(['a.id', 'a.parentId', 'a.articleId', 'a.content', 'a.createTime', 'b.username'])->from('articlecomment as a')->leftJoin('user as b', 'a.userId = b.id')->where(['commentId' => $post['id']])->orderBy(['createTime' => 'DESC', 'id' => 'DESC'])->createCommand()->queryAll();
         if ($comment) {
             foreach ($comment as $v) {
                 $content = '回复@' . Articlecomment::getCommentByParId($v['parentId']) . '：' . $v['content'];
                 $ahtml = html::a(html::tag("i", "", ["class" => "fa fa-thumbs-o-up"]) . html::tag("span", "回复"), ["/main/viewart", "id" => $v["articleId"], "parId" => $v['id']]);
                 $list .= '<div class="infos small-comment' . $post['id'] . '" style="border:1px solid;">
                         	<div class="media-body markdown-reply content-body">
                         		<p>' . $content . '</p>
                         		<span class="opts pull-right">
                         			<a class="author" >' . $v["username"] . '</a>
                         			•
                         			<addr title="' . $v["createTime"] . '">' . Html::tag("span", Yii::$app->formatter->asRelativeTime($v["createTime"])) . '</addr>
                         			' . $ahtml . '
                                 </span>
                         	</div>
                         </div>';
             }
         }
     }
     $result = array('success' => true, 'message' => $list);
     echo json_encode($result);
     die;
     return $this->renderAjax('index', ['success' => true, 'message' => '']);
 }

 public static function mostPopular()
 {
     $query = new Query();
     $query->select('meme_id, COUNT(meme_id) AS n_memes')->from('meme_vidmage')->groupBy('meme_id')->orderBy(['n_memes' => SORT_DESC])->limit(1);
     $row = $query->one();
     return self::findOne($row['meme_id']);
 }

 /**
  * Prepare the DB query
  *
  * @return $this
  */
 public function prepare()
 {
     // Throw a exception if a required parameter is not defined
     $this->verifyRequiredParameters();
     // Build the query
     $this->query = new Query();
     $this->query->select('*');
     $this->query->from($this->getTableName());
     $this->query->where('app_id=:appId', [':appId' => $this->appId]);
     return $this;
 }

 /**
  * Creates data provider instance with search query applied
  *
  * @param array $params
  *
  * @return ActiveDataProvider
  */
 public function search($params)
 {
     $query2 = new Query();
     if (isset($_GET['CallHistorySearch'])) {
         $monthStart = substr($_GET['CallHistorySearch']['start_time'], 5, 2);
         $monthEnd = substr($_GET['CallHistorySearch']['end_time'], 5, 2);
         $yearStart = substr($_GET['CallHistorySearch']['start_time'], 0, 4);
         $yearEnd = substr($_GET['CallHistorySearch']['end_time'], 0, 4);
         // cek interval month
         if ($monthStart == $monthEnd) {
             $obj = new CallHistory();
             $obj->setSuffixTable($yearStart . $monthStart);
             $query = $obj->find();
         } else {
             $obj = new CallHistory();
             $obj->setSuffixTable($yearStart . $monthStart);
             $query = $obj->find()->union($query2->select('*')->from('tbl_call_history_' . $yearEnd . $monthEnd), true);
             /* $obj = new CallHistory();
                $obj->setSuffixTable($yearStart.$monthStart);
                $query = new Query();
                $query2  = new Query();*/
             /*echo 'tbl_call_history_'.$yearStart.$monthStart;
                   echo 'tbl_call_history_'.$yearEnd.$monthEnd;
               die();*/
             $query->select('*')->from('tbl_csall_history_' . $yearStart . $monthStart);
             $query2->select('*')->from('tbl_call_history_' . $yearEnd . $monthEnd);
             $query->union($query2, true);
         }
     } else {
         // value default not result data
         $obj = new CallHistory();
         $obj->setSuffixTable('201509');
         $query = $obj->find();
     }
     /*$query = CallHistory::find()->union(
           $query2->select('*')
           ->from('tbl_call_history_201512')
       ,true);*/
     $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['forcePageParam' => false, 'pageSizeParam' => false, 'pageSize' => 10]]);
     $this->load($params);
     if (!$this->validate()) {
         // uncomment the following line if you do not want to return any records when validation fails
         // $query->where('0=1');
         return $dataProvider;
     }
     $query->andFilterWhere(['J' => $this->J, 'Al' => $this->Al, 'duration' => $this->duration, 'Bl' => $this->Bl]);
     $query->andFilterWhere(['like', 'Anumber', $this->Anumber])->andFilterWhere(['like', 'Bnumber', $this->Bnumber])->andFilterWhere(['like', 'Zon', $this->Zon])->andFilterWhere(['like', 'Trunk1', $this->Trunk1])->andFilterWhere(['like', 'Trunk2', $this->Trunk2])->andFilterWhere(['like', 'setup_time', $this->setup_time])->andFilterWhere(['between', 'start_time', $this->start_time, $this->end_time]);
     // ->andFilterWhere(['like', 'setup_time', $this->setup_time]);
     // echo "<pre>";
     // print_r($query);
     return $dataProvider;
 }

 /**
  * Получает список страниц для всплывающего окна
  * @return type
  */
 public function GetPagesList()
 {
     $query = new Query();
     $query->select(['p.name', 'm.name as module'])->join("LEFT JOIN", "module as m", "m.id = p.module")->from("backend__pages as p")->where(["p.visible" => 1, "p.active" => 1]);
     $result = $query->all();
     return $result;
 }

 public function actionPermissions($name)
 {
     $role = $this->auth->getRoles();
     $roles = array_keys($role);
     $query = new Query();
     $query->select('*')->from('auth_item')->leftJoin('auth_item_child', ['and', 'auth_item.name=auth_item_child.child', 'auth_item_child.parent not in (\'' . implode("','", $roles) . '\')'])->where(['auth_item.type' => 2])->andWhere(['not like', 'name', '/']);
     $models = $query->all();
     $targetHasModels = $this->auth->getPermissionsByRole($name);
     $targetHasModels = array_keys($targetHasModels);
     //取用户所有权限的集合
     $hasModel = [];
     foreach ($roles as $rkey => $subRole) {
         $subPermission = $this->auth->getPermissionsByRole($subRole);
         $hasModel = array_merge($hasModel, $subPermission);
     }
     $hasModels = array_keys($hasModel);
     $hasModelsTree = [];
     foreach ($models as $key => $model) {
         if (in_array($model['name'], $hasModels)) {
             array_push($hasModelsTree, $model);
         } else {
             $subModels = $this->getSubModels($model, $models);
             foreach ($subModels as $subKey => $subModel) {
                 if (in_array($subModel['name'], $hasModels)) {
                     array_push($hasModelsTree, $model);
                 }
             }
         }
     }
     return $this->render('permissions', ['models' => $hasModelsTree, 'hasModels' => $targetHasModels]);
 }

 public function retrieve($className, $primaryKey)
 {
     $tableName = call_user_func([$className, "tableName"]);
     $current = $className::find()->where(['id' => $primaryKey])->asArray()->one();
     $query = new Query();
     $query->select(['field_name', 'old_value', 'event', 'action_uuid', 'created_at']);
     $query->from($this->tableName);
     $query->where(['field_id' => $primaryKey, 'table_name' => $tableName]);
     $query->orderBy(['created_at' => SORT_ASC]);
     $changes = [];
     foreach ($query->all() as $element) {
         $uuid = $element['action_uuid'];
         if (!isset($changes[$uuid])) {
             $changes[$uuid] = $current;
         }
         $changes[$uuid][$element['field_name']] = $element['old_value'];
         $current = $changes[$uuid];
     }
     $models = array_map(function ($element) use($className) {
         $model = $className::instantiate($element);
         $className::populateRecord($model, $element);
         return $model;
     }, $changes);
     return new ArrayDataProvider(['allModels' => array_values($models)]);
 }

 public function getPrivilege($userId = null)
 {
     $db = new Query();
     $db->select("group_id")->from("privilege")->where("user_id = '{$userId}' ");
     $groupUser = $db->all();
     $GroupUserArr = array();
     foreach ($groupUser as $Guser) {
         $GroupUserArr[] = "'" . $Guser['group_id'] . "'";
     }
     $GroupUser = implode(",", $GroupUserArr);
     if (!empty($GroupUser)) {
         $GroupUserVal = $GroupUser;
     } else {
         $GroupUserVal = "''";
     }
     if (!empty($GroupUserVal)) {
         $Privilege = GroupPcu::find()->where('group_id IN(' . $GroupUserVal . ')')->all();
         $PcuArr = array();
         foreach ($Privilege as $rs) {
             $PcuArr[] = $rs['hospcode'];
         }
     } else {
         $PcuArr = null;
     }
     Yii::$app->session['privilege'] = $PcuArr;
 }

 public function actionProspek()
 {
     $regional = Regional::find()->all();
     $query = new Query();
     $data = array();
     foreach ($regional as $rows) {
         $query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE3.FIELD3 IS NOT NULL')->all();
         $handphone = $query->createCommand()->queryScalar();
         $query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE3.FIELD6 IS NOT NULL')->all();
         $email = $query->createCommand()->queryScalar();
         $query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->join('LEFT OUTER JOIN', 'TABLE2', 'TABLE2.FIELD1 = TABLE3.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE2.FIELD7 IS NOT NULL')->all();
         $phone = $query->createCommand()->queryScalar();
         array_push($data, array('regional' => $rows->FIELD1, 'handphone' => $handphone, 'email' => $email, 'phone' => $phone));
     }
     return $this->render('prospek', ['data' => $data, 'regional' => $regional]);
 }

 public static function getArticleCategories($articleId)
 {
     $query = new Query();
     $query->select('article_categories.*,article_categories_bind.article_id as active')->from('article_categories')->join('left outer join', 'article_categories_bind', 'article_categories.id = article_categories_bind.categories and
             article_categories_bind.article_id= ' . $articleId)->orderBy('article_categories.id');
     return $query->all();
 }

 /**
  * Updates an existing PageData model.
  * If update is successful, the browser will be redirected to the 'view' page.
  * @param integer $id_page
  * @param string $language
  * @return mixed
  */
 public function actionUpdate($id_page, $language)
 {
     $model = $this->findModel($id_page, $language);
     $model->setScenario('update');
     //if (!Yii::$app->user->can('ownAction', ['model' => $model])) {
     //    throw new ForbiddenHttpException('You are not allowed to perform this action.');
     //}
     /* copy to other languages */
     $languages = Message::getLocaleList();
     $q = new Query();
     $pageLang = $q->select(['language'])->from('{{%core_page_data}}')->where(['id_page' => $id_page])->all();
     foreach ($pageLang as $pl) {
         if (in_array($pl['language'], array_keys($languages))) {
             unset($languages[$pl['language']]);
         }
     }
     //var_dump($languages);
     /* submitted */
     if (Yii::$app->request->isPost) {
         /* page data */
         $model->load(Yii::$app->request->post());
         $model->page->load(Yii::$app->request->post());
         $model->page->save();
         $model->save();
         return $this->redirect(['update', 'id_page' => $model->id_page, 'language' => $model->language]);
     } else {
         return $this->render('update', ['model' => $model, 'languages' => $languages]);
     }
 }

 public function actionIndex($date = null)
 {
     //    $apteki=LogReestr::find()->where(['resstr' => 'apteki'])->all();
     if (!$date) {
         $date = date('Y-m');
     }
     $db = new Query();
     $db->from(LogReestr::tableName());
     $db->select(['log_reestr.created_at', 'log_reestr.address', 'log_reestr.resstr', 'log_reestr.id_resstr', 'log_reestr.action', 'log_reestr.name', 'log_reestr.ur_l_id', 'log_reestr.id_resstr', 'log_reestr.change', 'users.username', 'log_reestr.id_resstr']);
     $db->where(['=', 'resstr', 'apteki']);
     $db->leftJoin('users', "users.id = log_reestr.user");
     $db->orderBy('log_reestr.created_at DESC');
     $date_search = $date . '%';
     $db->andWhere(['like', 'log_reestr.created_at', $date_search, false]);
     $apteki = $db->all();
     //   $apteki_count = $db->count();
     $db = new Query();
     $db->from(LogReestr::tableName());
     $db->select(['log_reestr.created_at', 'log_reestr.address', 'log_reestr.resstr', 'log_reestr.id_resstr', 'log_reestr.name', 'log_reestr.action', 'log_reestr.change', 'users.username', 'log_reestr.id_resstr']);
     $db->where(['=', 'resstr', 'ur_l']);
     $db->leftJoin('users', "users.id = log_reestr.user");
     $db->andWhere(['like', 'log_reestr.created_at', $date_search, false]);
     $db->orderBy('log_reestr.created_at DESC');
     $ur_l = $db->all();
     // $ur_l_count = $db->count();
     $statm = \Yii::$app->db->createCommand("SELECT   users.username ,  COUNT(*) as count FROM  log_reestr  INNER JOIN users  ON users.id=log_reestr.user\n    where log_reestr.created_at like '" . $date . "%'\n        GROUP BY USER order by count DESC");
     $stat = $statm->queryAll();
     $statAllm = \Yii::$app->db->createCommand("SELECT COUNT(*) as count FROM  log_reestr\n    where log_reestr.created_at like '" . $date . "%'       ");
     $statAll = $statAllm->queryOne();
     return $this->render('index', ['apteki' => $apteki, 'ur_l' => $ur_l, 'date' => $date, 'stat' => $stat, 'statAll' => $statAll]);
 }

 public function getAllMatieres()
 {
     $query_mat = new Query();
     $query_mat->select('id,nom,nb_tours_complete, color, color_hl, color_rgb')->from('matiere')->orderBy('id');
     $matieres = $query_mat->all();
     return $this->arrayToMatiereList($matieres);
 }

 public function getIdByNumTitre($num, $titre)
 {
     $query = new Query();
     $query->select('id')->from('annales_track')->where('num = ' . $num . ' AND titre LIKE "' . $titre . '"');
     $data = $query->all();
     return $data[0]['id'];
 }

 public function getPhotos()
 {
     $query = new Query();
     $query->select('id, name, path')->from('{{%home_photo}}')->where('album_id=:id', [':id' => $this->id]);
     $photos = Tools::Pagination($query);
     return ['photos' => $photos['result'], 'pages' => $photos['pages']];
 }