PHP db\Query类代码示例

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

 /**
  * Returns calculated availability
  */
 public function getAvailability()
 {
     $query = new Query();
     $query->select('Product, Warehouse, Availability')->from('GetProductAvailability')->where("Product = {$this->ID}");
     $availability = $query->all();
     return $availability;
 }

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

 /**
  * Lists all Feed models.
  * @return mixed
  */
 public function actionIndex()
 {
     $query = new Query();
     $query->select('id, content, feed_data, template, created_at')->from('{{%home_feed}}')->where('user_id=:user_id', [':user_id' => Yii::$app->user->id])->orderBy('created_at DESC');
     $pages = Tools::Pagination($query);
     return $this->render('index', ['feeds' => $pages['result'], 'pages' => $pages['pages']]);
 }

 /**
  * @return string
  */
 public function actionSendorder()
 {
     $session = Yii::$app->session;
     $session->open();
     $model = new Orders();
     $query = new Query();
     if ($model->load(Yii::$app->request->post()) && $model->validate()) {
         $model->total_sum = $_SESSION['total_price'];
         $model->date = date('d-m-Y');
         $model->time = Yii::$app->getFormatter()->asTime(time());
         $id = array_keys($_SESSION['cart']);
         $model->complite = 0;
         for ($i = 0; $i < count($id); $i++) {
             $products[] = $query->select('title')->from('data')->where(['id' => $id[$i]])->one();
             $products[$i]['quantity'] = $_SESSION['cart'][$id[$i]]['quant'];
         }
         $model->adres = strip_tags($model->adres);
         $model->product = serialize($products);
         $model->save();
         //				$session->destroy();
         //////
         $mrh_login = Yii::$app->params['mrh_login'];
         $mrh_pass1 = Yii::$app->params['mrh_pass1'];
         $inv_id = $model->id;
         $inv_desc = Yii::$app->params['inv_desc'];
         $out_summ = $model->total_sum;
         $crc = md5("{$mrh_login}:{$out_summ}:{$inv_id}:{$mrh_pass1}");
         return $this->render('pay', ['name' => $model->name, 'out_summ' => $out_summ, 'mrh_login' => $mrh_login, 'inv_id' => $inv_id, 'inv_desc' => $inv_desc, 'crc' => $crc]);
     } else {
         return $this->render('error', ['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 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']];
 }

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

 /**
  * setup search function for filtering and sorting
  * based on fullName field
  */
 public function search($params, Query $query)
 {
     $dataProvider = new ActiveDataProvider(['query' => $query]);
     /**
      * Setup your sorting attributes
      * Note: This is setup before the $this->load($params)
      * statement below
      */
     $dataProvider->setSort(['attributes' => ['action' => ['label' => T::t('Action'), 'asc' => ['action' => SORT_ASC], 'desc' => ['action' => SORT_DESC], 'default' => SORT_ASC], 'created' => ['label' => T::t('Date'), 'asc' => ['created' => SORT_ASC, 'action' => SORT_ASC], 'desc' => ['created' => SORT_DESC, 'action' => SORT_ASC], 'default' => SORT_DESC], 'task_price' => ['label' => T::t('Reward'), 'asc' => ['task_price' => SORT_ASC, 'action' => SORT_ASC], 'desc' => ['task_price' => SORT_DESC, 'action' => SORT_ASC], 'default' => SORT_DESC]]]);
     if (!($this->load($params) && $this->validate())) {
         return $dataProvider;
     }
     if (isset($params[$this->formName()])) {
         foreach ($params[$this->formName()] as $prop => $propValue) {
             if ("" != $propValue && property_exists($this, $prop)) {
                 /* map objet_id to type */
                 if (in_array($prop, ['object_id', 'task_price'])) {
                     $query->andWhere(sprintf('%s=%d', $prop, $this->{$prop}));
                 } else {
                     $query->andWhere(sprintf('%s LIKE "%%%s%%"', $prop, $this->{$prop}));
                 }
             }
         }
     }
     return $dataProvider;
 }

 public function getAllCas()
 {
     $query = new Query();
     $query->select('num,cpt,sumnote,year')->from('aggreg_cas_weekly')->orderBy('num ASC');
     $data = $query->all();
     return $this->arrayToWeekCas($data);
 }

 public function getPsychologistProblemsList($psychologistId)
 {
     $query = new Query();
     $query->select('problems.*')->from('problems')->join('left outer join', 'psychologist_problems', 'problems.id = psychologist_problems.problem_id and
             psychologist_problems.psychologist_id= ' . $psychologistId)->orderBy('problems.id');
     return $query->all();
 }

 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 static function getUsersModulesList()
 {
     $query = new Query();
     $query->select('modules.*,users_modules.user_id as active')->from('modules')->join('left join', 'users_modules', 'modules.id = users_modules.module_id and
             users_modules.user_id= ' . Yii::$app->user->id)->orderBy('modules.id');
     return $query->all();
 }

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