PHP Query::orderBy方法代码示例

 public function beforeInsert()
 {
     $last = $this->query->orderBy([$this->orderAttribute => SORT_DESC])->limit(1)->one();
     if ($last === null) {
         $this->owner->{$this->orderAttribute} = 1;
     } else {
         $this->owner->{$this->orderAttribute} = $last->{$this->orderAttribute} + 1;
     }
 }

 /**
  * Creates data provider instance with search query applied
  *
  * @param array $params
  *
  * @return ActiveDataProvider
  */
 public function search($params)
 {
     $this->buildSearchQuery();
     $this->applyContextFilters();
     $dataProvider = new ActiveDataProvider(['query' => $this->query, 'pagination' => ['pageSize' => $this->pageSize]]);
     $this->load($params);
     if (!$this->validate()) {
         return $dataProvider;
     }
     $this->applySearchFilters();
     $this->query->orderBy('date_retrieved DESC');
     return $dataProvider;
 }

 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 actionIndex($user = null, $date = null, $trimestr = null, $scroll = null)
 {
     if (\Yii::$app->user->identity->status == 3) {
         $user = \Yii::$app->user->identity->id;
     }
     //$model = Preparats::find()->where(['region_id' => $region])->orderBy('name')->all();
     if ($user) {
         //$apteki = Apteki::find()->where(['pi_id' => $user, 'farmopeka' => '1'])->orderBy('address')->all();
         $query = new Query();
         $query->select(['ur_l.name', 'apteki.address', 'apteki.id']);
         $query->from('apteki');
         $query->LeftJoin('ur_l', 'ur_l.id = apteki.ur_l_id');
         $query->andFilterWhere(['=', 'apteki.farmopeka', '1']);
         $query->andFilterWhere(['=', 'apteki.pi_id', $user]);
         //$query->orderBy('ur_l.name,apteki.address');
         $query->orderBy(['ur_l.name' => SORT_ASC, 'apteki.address' => SORT_ASC]);
         $apteki = $query->all();
     }
     if (!$date) {
         $date = date('Y-m-01');
     }
     //   if (!trimestr) $trimestr = Visitors::getTrimestr($date);
     if ($user) {
         $table = Visitors::getTable($user, $apteki, $date, $trimestr, $scroll);
     }
     // print \Yii::$app->params['infoEmail'];
     //Количество привязвнных ир.лиц и розничных точек
     $count['ur'] = Ur::find()->where(['pi_id' => $user])->andWhere(['farmopeka' => 1])->count();
     $count['apteki'] = Apteki::find()->where(['pi_id' => $user])->andWhere(['farmopeka' => 1])->count();
     return $this->render('index', ['user' => $user, 'table' => $table, 'date' => $date, 'trimestr' => $trimestr, 'count' => $count, 'scroll' => $scroll]);
 }

 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 static function getRegion()
 {
     $db = new yii\db\Query();
     $db->select('id', 'name');
     $db->from('region');
     $db->orderBy('name');
     return $db->all();
 }

 public function beforeInsert()
 {
     if (!is_null($this->where)) {
         if (!is_array($this->where)) {
             $this->where = array($this->where);
         }
         foreach ($this->where as $where) {
             $this->query->andWhere([$where => $this->owner->{$where}]);
         }
     }
     $last = $this->query->orderBy([$this->orderAttribute => SORT_DESC])->limit(1)->one();
     if ($last === null) {
         $this->owner->{$this->orderAttribute} = 1;
     } else {
         $this->owner->{$this->orderAttribute} = $last->{$this->orderAttribute} + 1;
     }
 }

 public static function getMostActive()
 {
     $query = new Query();
     $query->select('profile.*,(SELECT  COUNT(*) FROM discussion_post_reply WHERE profile.user_id = discussion_post_reply.user_id) as repliesCount');
     $query->from('profile');
     $query->orderBy('repliesCount DESC');
     $query->limit('4');
     return $query->all();
 }

 public static function getPopularVideos()
 {
     $query = new Query();
     $query->select('videos.*,(SELECT  COUNT(*) FROM videos_comments WHERE videos.id = videos_comments.video_id) as commentsCount');
     $query->from('videos');
     $query->orderBy('commentsCount DESC');
     $query->limit('4');
     return $query->all();
 }

 public static function getPopularPosts()
 {
     $query = new Query();
     $query->select('article.*,(SELECT  COUNT(*) FROM article_comments WHERE   article.id = article_comments.article_id) as commentsCount');
     $query->from('article');
     $query->orderBy('commentsCount DESC');
     $query->limit('4');
     return $query->all();
 }

 /**
  * @param Query $query
  * @param array $params
  * @return Query
  */
 protected function additionalFilter($query, $params)
 {
     if (!isset($params['sort'])) {
         $query->orderBy(['date' => SORT_DESC, 'created_at' => SORT_DESC, 'id' => SORT_DESC]);
     } else {
         switch ($params['sort']) {
             case 'total':
                 $query->orderBy(['type_id' => SORT_DESC, 'total' => SORT_ASC]);
                 break;
             case '-total':
                 $query->orderBy(['type_id' => SORT_ASC, 'total' => SORT_DESC]);
                 break;
         }
     }
     $period = Yii::$app->request->get('period');
     if (isset($period)) {
         switch ($period) {
             case 'today':
                 $today = Yii::$app->getFormatter()->asDate('now', "php:Y-m-d");
                 $query->andFilterWhere(['like', 'date', $today]);
                 break;
             case 'yesterday':
                 $yesterday = date('Y-m-d', strtotime(date('Y-m-d') . " - 1 day"));
                 $query->andFilterWhere(['like', 'date', $yesterday]);
                 break;
             case 'current_month':
                 $query = $this->setDefaultPeriod($query);
                 break;
             case 'all':
                 break;
             default:
                 $query = $this->setDefaultPeriod($query);
                 break;
         }
     } else {
         $query = $this->setDefaultPeriod($query);
     }
     if (isset($params['category'])) {
         $query->leftJoin(Transaction2Category::tableName(), 'transaction_id = id');
         $query->andWhere(['transaction2category.category_id' => $params['category']]);
     }
     return $query;
 }

 public function getHistory($chatId, $limit = 10)
 {
     $query = new Query();
     $query->select(['user_id', 'username', 'message', 'timestamp', 'avatar_16', 'avatar_32'])->from(self::tableName())->where(['chat_id' => $chatId]);
     $query->orderBy(['timestamp' => SORT_DESC]);
     if ($limit) {
         $query->limit($limit);
     }
     return $query->all();
 }

 public function search($params)
 {
     $query = new \yii\db\Query();
     $dataProvider = new ActiveDataProvider(['query' => $query->from(IisAccessLog::tableName()), 'pagination' => ['pageSize' => 50]]);
     $query->orderBy('Id desc');
     $this->load($params);
     if (!$this->validate()) {
         return $dataProvider;
     }
     if ($this->date_reg) {
         $this->start_date = $this->date_reg;
         $this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date)));
     }
     $query->andFilterWhere(['Ip1' => $this->Ip1]);
     $query->andFilterWhere(['>=', 'date_reg', $this->start_date]);
     $query->andFilterWhere(['<', 'date_reg', $this->end_date]);
     $query->orderBy('Id desc');
     return $dataProvider;
 }

 public function search($params)
 {
     $query = new \yii\db\Query();
     $dataProvider = new ActiveDataProvider(['query' => $query->from(AccessLogIismost::tableName()), 'pagination' => ['pageSize' => 50]]);
     $query->orderBy('AccessIPNum desc');
     $this->load($params);
     if (!$this->validate()) {
         return $dataProvider;
     }
     if ($this->Date_time) {
         $this->start_date = $this->Date_time;
         $this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date)));
     }
     $query->andFilterWhere(['Website' => $this->Website]);
     $query->andFilterWhere(['server' => $this->server]);
     $query->andFilterWhere(['>=', 'Date_time', $this->start_date]);
     $query->andFilterWhere(['<', 'Date_time', $this->end_date]);
     $query->orderBy('AccessIPNum desc');
     return $dataProvider;
 }

 public static function getSingleCategoryWithPosts($categoryId)
 {
     $query = new Query();
     $query->select('discussion_posts.*,discussion_categories.*,discussion_posts.id as dpId');
     $query->from('discussion_categories');
     $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 8]]);
     $query->andFilterWhere(['discussion_categories.id' => $categoryId]);
     $query->join('left join', 'discussion_posts', 'discussion_posts.discussion_category_id=discussion_categories.id');
     $query->orderBy('discussion_posts.id DESC');
     return $dataProvider;
 }