PHP Query::from方法代码示例

 public function actionIndex()
 {
     $query = new Query();
     /*$department = $query->select(['department_id','department_name'])
       ->from('department')
       ->all();*/
     //$department = $query->from('department')->all(); //เทียบได้กับโดยไม่ต้องใส่ select ใส่ from เลย
     //yii จะใส่ select ให้ SELECT * FROM department
     /*$search ='กลุ่ม'
       $department = $query->select(['department_id','department_name'])
                           ->from('department')
                           ->where([
                               '>like','department_id',$search
                               ])
                           ->all();*/
     //order by อีกหนึ่งวิธี
     //$department = $query->from('department_id')->orderBy('department_id DESC')->all();
     $department = $query->from('department')->orderBy(['department_name' => SORT_ASC])->all();
     $count = $query->from('department')->count();
     $max = $query->from('department')->max('department_id');
     $sum = $query->from('department')->sum('department_id');
     // echo sql
     $sql = $query->from('department')->orderBy(['department_name' => SORT_ASC])->createCommand();
     return $this->render('index', ['departments' => $department, 'count' => $count, 'max' => $max, 'sum' => $sum, 'sql' => $sql]);
 }

 /**
  * 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;
 }

 public function run()
 {
     $str = '';
     $query = new Query();
     $res = $query->select('sum(click) as click')->from('blog_post')->one();
     $click = $res['click'];
     $str .= '<div class="site-stat">点击数量：' . $click . '</div>';
     $postCount = $query->from('blog_post')->count();
     $str .= '<div class="site-stat">文章数量：' . $postCount . '</div>';
     $commentCount = $query->from('blog_comment')->count();
     $str .= '<div class="site-stat">评论数量：' . $commentCount . '</div>';
     return $this->render('portal', ['title' => $this->title, 'content' => $str]);
 }

 /**
  * @return string
  */
 public function actionIndex()
 {
     $this->layout = "bootstrap";
     $query = new Query();
     $query_advert = $query->from('advert')->orderBy('id desc');
     $command = $query_advert->limit(5);
     $result_general = $command->all();
     $count_general = $command->count();
     $featured = $query_advert->limit(15)->all();
     $recommend_query = $query_advert->where('recommend = 1')->limit(5);
     $recommend = $recommend_query->all();
     $recommend_count = $recommend_query->count();
     return $this->render('index', ['result_general' => $result_general, 'count_general' => $count_general, 'featured' => $featured, 'recommend' => $recommend, 'recommend_count' => $recommend_count]);
     /*
     $command = $query->from('advert')->orderBy('id desc')->limit(5);
     $result_general = $command->all();
     $count_general = $command->count();
     
     return $this->render('index', ['result_general' => $result_general, 'count_general' => $count_general]);
     */
     //$this->layout = "inner";
     /*
     $locator = \Yii::$app->locator;
     $cache = $locator->cache;
     
     $cache->set('test', 1);
     
     print $cache->get('test');
     */
     //return $this->render('index');
 }

 public static function getPostByTags()
 {
     $query = new Query();
     $query->from('tags')->join('JOIN', 'posts_tags', 'posts_tags.tag_id = tags.id')->join('JOIN', 'posts', 'posts_tags.post_id = posts.id')->where(['tags.name' => $_GET['tags']]);
     $rows = $query->all();
     return $rows;
 }

 /**
  * Lists all Job models.
  * @return mixed
  */
 public function actionIndex()
 {
     $model = new Job();
     // This is used to search/filter organizations
     $dataProvider = null;
     if (isset($_GET['user_id'])) {
         $user_id = $_GET['user_id'];
         if ($user_id !== null) {
             $query = new Query();
             $query->from(Job::tableName());
             $query->where(['user_id' => $user_id]);
             $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]);
         }
     } else {
         $dataProvider = new ActiveDataProvider(['query' => Job::find(), 'pagination' => ['pageSize' => 5]]);
     }
     if ($model->load(Yii::$app->request->post())) {
         $query = new Query();
         $query->from(Job::tableName());
         if (!is_null($model->employment_type) && is_array($model->employment_type)) {
             $query->where(['employment_type' => array_map('intval', $model->employment_type)]);
         }
         if (!is_null($model->job_type) && is_array($model->job_type)) {
             $query->where(['job_type' => array_map('intval', $model->job_type)]);
         }
         if (!is_null($model->work_domain) && is_array($model->work_domain)) {
             $query->andWhere(['work_domain' => array_map('intval', $model->work_domain)]);
         }
         if (isset($_GET['user_id'])) {
             $query->andWhere(['user_id' => $_GET['user_id']]);
         }
         $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]);
     }
     return $this->render('index', ['model' => $model, 'dataProvider' => $dataProvider]);
 }

 public function actionTraining($id)
 {
     $q = new Query();
     $q->from('train')->where('HeroId=:id', array(':id' => $id));
     $training = $q->all();
     return json_encode($training);
 }

 /**
  * @inheritdoc
  */
 public function actionAddHint()
 {
     Yii::$app->response->format = Response::FORMAT_JSON;
     $id = (int) Yii::$app->request->post('id');
     if (Yii::$app->user->isGuest || $this->module->storage === Hint::TYPE_COOKIE) {
         $tooltips = Yii::$app->request->cookies->getValue($this->module->cookieName);
         if (!is_array($tooltips)) {
             $tooltips = [];
         }
         if (!isset($tooltips[$id])) {
             $tooltips[$id] = 1;
             $options['name'] = $this->module->cookieName;
             $options['value'] = $tooltips;
             $options['expire'] = time() + 86400 * 365;
             $cookie = new \yii\web\Cookie($options);
             Yii::$app->response->cookies->add($cookie);
         }
     } else {
         $query = new Query();
         $res = $query->from($this->module->userTooltipTable)->where(['user_id' => Yii::$app->getUser()->getId(), 'source_message_id' => $id])->exists();
         if (!$res) {
             Yii::$app->db->createCommand()->insert($this->module->userTooltipTable, ['user_id' => Yii::$app->getUser()->getId(), 'source_message_id' => $id])->execute();
         }
     }
     return ['r' => 1];
 }

 /**
  * Initializes the DbMessageSource component.
  */
 public function init()
 {
     parent::init();
     if ($this->autoInsert) {
         $this->on(static::EVENT_MISSING_TRANSLATION, function ($event) {
             if (!isset($this->messagesId[$event->message])) {
                 $query = new Query();
                 $id = $query->select("id")->from($this->sourceMessageTable)->where(['category' => $event->category, 'message' => $event->message])->scalar($this->db);
                 if ($id === false) {
                     $this->db->createCommand()->insert($this->sourceMessageTable, ['category' => $event->category, 'message' => $event->message])->execute();
                     $id = $this->db->lastInsertID;
                 }
                 /* @var $i18n I18N */
                 $i18n = Yii::$app->i18n;
                 $languages = $i18n->getLanguages();
                 foreach ($languages as $language_id => $language) {
                     $query = new Query();
                     $exists = $query->from($this->messageTable)->where(['id' => $id, 'language_id' => $language_id])->exists($this->db);
                     if (!$exists) {
                         $this->db->createCommand()->insert($this->messageTable, ['id' => $id, 'language_id' => $language_id, 'translation' => ''])->execute();
                     }
                 }
                 $this->messagesId[$event->message] = $id;
             }
             $event->translatedMessage = $event->message;
         });
     }
 }

 public function search_details($params)
 {
     $p['store_id'] = isset($params['store_id']) ? $params['store_id'] : 109;
     $p['detailnumber'] = isset($params['article']) ? $params['article'] : '';
     $query = new Yii\db\Query();
     return $query->from('finddetails')->where($p)->all();
 }

 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]);
     }
 }

 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 actionCurrent_list()
 {
     $query = new Query();
     $list_id = Yii::$app->request->post('listID');
     $listInfo = $query->from('user_list')->select('*')->where(['userId' => Yii::$app->user->id, 'list_id' => $list_id])->all();
     echo json_encode($listInfo);
 }

 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 actionIndex($id)
 {
     $session = \Yii::$app->session;
     $request = \Yii::$app->request;
     $donateForm = new DonateForm();
     $buyForm = new BuyForm();
     $supportForm = new SupportForm();
     if ($donateForm->load($request->post()) && $donateForm->submit()) {
         $session->set('donate', $request->post('DonateForm'));
         return $this->redirect('@web/index.php?r=support/donate');
     } else {
         if ($buyForm->load($request->post()) && $buyForm->submit()) {
             $session->set('bought', $request->post('BuyForm'));
             return $this->redirect('@web/index.php?r=support/buy');
         } else {
             if ($supportForm->load($request->post()) && $supportForm->submit()) {
                 $session->set('motive', $request->post('SupportForm'));
                 return $this->redirect('@web/index.php?r=support/motivation');
             }
         }
     }
     $query = new Query();
     // compose the query
     $query->from('hero')->where('Hid=:id', array(':id' => $id));
     $hero = $query->one();
     $session->set('hero', $hero);
     return $this->render('support', array('hero' => $hero));
 }