PHP Query::limit方法代码示例

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

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

 /**
  * Returns list of users' full names
  * @param int $limit records count
  * @return array list of users' names
  */
 public static function getUserList($limit = null)
 {
     $query = new Query();
     $query->select(['id', 'text' => "CONCAT(`u`.`first_name`,' ', `u`.`last_name`)"])->from(['u' => 'User']);
     if (isset($limit)) {
         $query->limit($limit);
     }
     $query->each();
     $command = $query->createCommand();
     return $command->queryAll();
 }

 public function getTopPsychologists($amount = null)
 {
     $query = new Query();
     $query->select('profile.*')->from('profile')->join('join', 'psychologist_top', 'psychologist_top.psychologist_id = profile.user_id')->orderBy('profile.id');
     if ($amount) {
         $query->limit($amount);
         return $psychologistsTopDataProvider = new ActiveDataProvider(['query' => $query]);
         //return $query->all();
     }
     return $psychologistsTopDataProvider = new ActiveDataProvider(['query' => $query]);
 }

 private function getWhere($filters = [], $select = '*')
 {
     $query = new Query();
     $query->select($select)->from(static::tableName());
     if (isset($filters['offset']) && $filters['offset']) {
         $query->offset($filters['offset']);
     }
     if (isset($filters['limit']) && $filters['limit']) {
         $query->limit($filters['limit']);
     }
     return $query;
 }

 /**
  * Получает записи с базы данных
  * @param string $where
  * @param string $column
  */
 public function GetItems($columns = [], $where = [], $limit = 0)
 {
     $query = new Query();
     $query->select($columns);
     $query->from($this->table);
     if ($where) {
         $query->where($where);
     }
     if ($limit) {
         $query->limit($limit);
     }
     $result = $query->all();
     return $result;
 }

 private function getWhere($filters = [], $select = '*')
 {
     $query = new Query();
     $query->select($select)->from(static::tableName());
     if (isset($filters['D05_DEN_NO']) && $filters['D05_DEN_NO']) {
         $query->andwhere('D05_DEN_NO=:den_no', [':den_no' => $filters['D05_DEN_NO']]);
     }
     if (isset($filters['offset']) && $filters['offset']) {
         $query->offset($filters['offset']);
     }
     if (isset($filters['limit']) && $filters['limit']) {
         $query->limit($filters['limit']);
     }
     return $query;
 }

 /**
  * @return array
  * @throws yii\web\NotFoundHttpException
  */
 public function run()
 {
     if (!isset($_GET[$this->query_variable])) {
         throw new yii\web\NotFoundHttpException();
     }
     $modelName = $this->modelName;
     Yii::$app->response->format = yii\web\Response::FORMAT_JSON;
     $query = new yii\db\Query();
     $query->select($this->json_attributes)->from($modelName::tableName());
     $search_query = $_GET[$this->query_variable];
     foreach ($this->search_attributes as $attribute) {
         $query->orWhere(['like', $attribute, $search_query]);
     }
     $suggest = $query->limit($this->limit)->all();
     return $suggest;
 }

 private function getWhere($filters = [], $select = '*')
 {
     $query = new Query();
     $query->select($select)->from(static::tableName());
     if (count($filters)) {
         foreach ($filters as $field => $val) {
             if ($field != 'offset' && $field != 'limit') {
                 $query->andwhere($field . ' = ' . $val);
             }
         }
     }
     //$query->where('status=:status', [':status' => $status]);
     if (isset($filters['offset']) && $filters['offset']) {
         $query->offset($filters['offset']);
     }
     if (isset($filters['limit']) && $filters['limit']) {
         $query->limit($filters['limit']);
     }
     return $query;
 }

 /**
  * @param array $filters
  * @param string $select
  * @return Query
  */
 private function getWhere($filters = [], $select = '*')
 {
     $query = new Query();
     $query->select($select)->from(static::tableName());
     if (isset($filters['M08_HAN_CD']) && $filters['M08_HAN_CD']) {
         $query->where('M08_HAN_CD=:han_cd', [':han_cd' => $filters['M08_HAN_CD']]);
     }
     if (isset($filters['M08_SS_CD']) && $filters['M08_SS_CD']) {
         $query->where('M08_SS_CD=:ss_cd', [':ss_cd' => $filters['M08_SS_CD']]);
     }
     if (isset($filters['M08_JYUG_CD']) && $filters['M08_JYUG_CD']) {
         $query->where('M08_JYUG_CD=:jy_cd', [':jy_cd' => $filters['M08_JYUG_CD']]);
     }
     if (isset($filters['offset']) && $filters['offset']) {
         $query->offset($filters['offset']);
     }
     if (isset($filters['limit']) && $filters['limit']) {
         $query->limit($filters['limit']);
     }
     return $query;
 }

 /**
  * Lists all UrQuestions models.
  * @return mixed
  */
 public function actionIndex()
 {
     //топ аптек
     $db = new Query();
     $db->from('ur_questions');
     $db->select(['COUNT(*) AS count', 'ur_l_id', 'ur_l.name']);
     $db->leftJoin('ur_l', "ur_l.id=ur_questions.`ur_l_id`");
     $db->groupBy('ur_l_id');
     $db->orderBy('count DESC');
     $db->limit(5);
     $top = $db->all();
     $query = (new \yii\db\Query())->from('ur_questions');
     $query->select('username,ur_questions.id,ur_questions.created_at,question,ur_l.name,date_ansver');
     $query->leftJoin('ur_l', 'ur_questions.ur_l_id = ur_l.id');
     $query->leftJoin('users', 'ur_questions.user_id = users.id');
     if (\Yii::$app->user->identity->status != 1) {
         // $query->where(['ur_questions.user_id'=>\Yii::$app->user->identity->id]);
     }
     $query->orderBy(['ur_questions.id' => SORT_DESC]);
     $dataProvider = new ActiveDataProvider(['key' => 'id', 'query' => $query->from('ur_questions')]);
     return $this->render('index', ['dataProvider' => $dataProvider, 'top' => $top]);
 }

 private function getWhere($filters = [], $select = '*')
 {
     $query = new Query();
     $query->select($select)->from(static::tableName());
     //$query->where('status=:status', [':status' => $status]);
     if (isset($filters['D01_KAIIN_CD']) && $filters['D01_KAIIN_CD']) {
         $query->andwhere('D01_KAIIN_CD = ' . $filters['D01_KAIIN_CD']);
     }
     if (isset($filters['D01_CUST_NO']) && $filters['D01_CUST_NO']) {
         $query->andwhere('D01_CUST_NO = ' . $filters['D01_CUST_NO']);
     }
     if (isset($filters['D01_KAKE_CARD_NO']) && $filters['D01_KAKE_CARD_NO']) {
         $query->where(['D01_KAKE_CARD_NO' => $filters['D01_KAKE_CARD_NO']]);
     }
     if (isset($filters['offset']) && $filters['offset']) {
         $query->offset($filters['offset']);
     }
     if (isset($filters['limit']) && $filters['limit']) {
         $query->limit($filters['limit']);
     }
     return $query;
 }

 public static function getList($page = 1, $rows = 10, $condition = '', $conditionParams = [])
 {
     $return = [];
     $query = new Query();
     $query->select('COUNT(1)');
     $query->from(self::tableName() . ' t0');
     $query->where($condition, $conditionParams);
     $result['total'] = $query->scalar();
     $query->select(['t0.id', 't0.username', 't0.email', 't1.firstname', 't1.lastname']);
     $query->join('LEFT JOIN', 'user_profile t1', 't0.id = t1.user_id');
     $query->offset($page * $rows - $rows);
     $query->limit($rows);
     $result['rows'] = $query->All();
     foreach ($result['rows'] as $k => $v) {
         $myRoles = Yii::$app->authManager->getRolesByUser($v['id']);
         if (count($myRoles)) {
             foreach ($myRoles as $kk => $vv) {
                 $result['rows'][$k]['role'] = $kk;
                 break;
             }
         }
     }
     return $result;
 }