PHP Query::offset方法代码示例

 /**
  * Lists all Airport models.
  * @return mixed
  */
 public function actionIndex()
 {
     $params = $_REQUEST;
     $filter = array();
     $sort = "";
     $page = 1;
     $limit = 10;
     if (isset($params['page'])) {
         $page = $params['page'];
     }
     if (isset($params['limit'])) {
         $limit = $params['limit'];
     }
     $offset = $limit * ($page - 1);
     /* Filter elements */
     if (isset($params['filter'])) {
         $filter = (array) json_decode($params['filter']);
     }
     if (isset($params['datefilter'])) {
         $datefilter = (array) json_decode($params['datefilter']);
     }
     if (isset($params['sort'])) {
         $sort = $params['sort'];
         if (isset($params['order'])) {
             if ($params['order'] == "false") {
                 $sort .= " desc";
             } else {
                 $sort .= " asc";
             }
         }
     }
     $query = new Query();
     if (isset($params['airport_code'])) {
         $query->offset($offset)->limit($limit)->from('airport')->andFilterWhere(['like', 'airport_code', $params['airport_code']])->orderBy($sort)->select("id,airport_code,airport_name,country,city");
     } else {
         $query->offset($offset)->limit($limit)->from('airport')->orderBy($sort)->select("id,airport_code,airport_name,country,city");
     }
     /*    if(!empty($filter)){
           $query->offset($offset)
                            ->limit($limit)
                            ->from('airport')
                      //      ->andFilterWhere(['like', 'id', $filter['id']])
                            ->andFilterWhere(['like', 'airport_code', $filter['airport_code']])
                        //    ->andFilterWhere(['like', 'airport_name', $filter['airport_name']])
                            ->orderBy($sort)
                            ->select("id,airport_code,airport_name,country,city");
           }else{ */
     /* }*/
     $command = $query->createCommand();
     $models = $command->queryAll();
     $totalItems = $query->count();
     $this->setHeader(200);
     echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT);
 }

 /**
  * Lists all City models.
  * @return mixed
  */
 public function actionIndex()
 {
     $params = $_REQUEST;
     $filter = array();
     $sort = "";
     $page = 1;
     $limit = 10;
     if (isset($params['page'])) {
         $page = $params['page'];
     }
     if (isset($params['limit'])) {
         $limit = $params['limit'];
     }
     $offset = $limit * ($page - 1);
     /* Filter elements */
     if (isset($params['filter'])) {
         $filter = (array) json_decode($params['filter']);
     }
     /*
                  echo "<pre>";
                 print_r($params);
                 echo "</pre>";
                 
                 echo "<pre>";
                 print_r($filter);
                 echo "</pre>";
                 exit;*/
     if (isset($params['datefilter'])) {
         $datefilter = (array) json_decode($params['datefilter']);
     }
     if (isset($params['sort'])) {
         $sort = $params['sort'];
         if (isset($params['order'])) {
             if ($params['order'] == "false") {
                 $sort .= " desc";
             } else {
                 $sort .= " asc";
             }
         }
     }
     $query = new Query();
     $query->offset($offset)->limit($limit)->from('user')->andFilterWhere(['like', 'id', $filter['id']])->andFilterWhere(['like', 'name', $filter['name']])->andFilterWhere(['like', 'age', $filter['age']])->orderBy($sort)->select("id,name,age,createdAt,updatedAt");
     if ($datefilter['from']) {
         $query->andWhere("createdAt >= '" . $datefilter['from'] . "' ");
     }
     if ($datefilter['to']) {
         $query->andWhere("createdAt <= '" . $datefilter['to'] . "'");
     }
     $command = $query->createCommand();
     $models = $command->queryAll();
     $totalItems = $query->count();
     $this->setHeader(200);
     echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT);
 }

 /**
  * Lists all ExploreRecommend models.
  * @return mixed
  */
 public function actionIndex()
 {
     $query = new Query();
     $query->select('id, summary, created_at, title, username, user_id')->from('{{%explore_recommend}}')->where(['category' => 'post'])->orderBy('id DESC');
     $pages = new \yii\data\Pagination(['totalCount' => $query->count()]);
     $posts = $query->offset($pages->offset)->limit($pages->limit)->all();
     $forums = new Query();
     $forums = $forums->select('f.forum_name, f.forum_url, f.forum_desc, f.forum_icon, e.id')->from('{{%forum}} as f')->join('LEFT JOIN', '{{%explore_recommend}} as e', 'e.table_id=f.id')->where(['e.category' => 'forum'])->orderBy('e.id DESC')->all();
     $albums = new Query();
     $albums = $albums->select('id, table_id')->from('{{%explore_recommend}}')->where(['category' => 'album'])->orderBy('id DESC')->all();
     return $this->render('index', ['posts' => $posts, 'pages' => $pages, 'forums' => $forums, 'albums' => $albums]);
 }

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

 public function actionIndex()
 {
     $params = $_REQUEST;
     //TODO Calling unknown method: yii\web\Request::getParams() why?
     //$params = \Yii::$app->request->getParams();
     var_dump($params);
     exit;
     $filter = array();
     $sort = "";
     $page = 1;
     $limit = 10;
     if (isset($params['page'])) {
         $page = $params['page'];
     }
     if (isset($params['limit'])) {
         $limit = $params['limit'];
     }
     $offset = $limit * ($page - 1);
     /* Filter elements */
     if (isset($params['filter'])) {
         $filter = (array) json_decode($params['filter']);
     }
     if (isset($params['datefilter'])) {
         $datefilter = (array) json_decode($params['datefilter']);
     }
     if (isset($params['sort'])) {
         $sort = $params['sort'];
         if (isset($params['order'])) {
             if ($params['order'] == "false") {
                 $sort .= " desc";
             } else {
                 $sort .= " asc";
             }
         }
     }
     $query = new Query();
     $query->offset($offset)->limit($limit)->from('user')->orderBy($sort)->select("*");
     /*if ($datefilter['from']) {
           $query->andWhere("createdAt >= '" . $datefilter['from'] . "' ");
       }
       if ($datefilter['to']) {
           $query->andWhere("createdAt <= '" . $datefilter['to'] . "'");
       }*/
     $command = $query->createCommand();
     $models = $command->queryAll();
     $totalItems = $query->count();
     $this->setHeader(200);
     echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT);
 }

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

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

 public static function queryDatas($tableName, $conditions, $offset, $size, $orderby, $isGetTotal = false)
 {
     $query = new Query();
     $query->select('*')->from($tableName);
     if (!empty($conditions)) {
         foreach ($conditions as $name => $value) {
             if (is_array($value)) {
                 $query->andWhere($value);
             } else {
                 $query->andWhere([$name => $value]);
             }
         }
     }
     $total = 0;
     if ($isGetTotal) {
         $total = $query->count('*');
     }
     $query->offset($offset)->limit($size)->orderBy($orderby);
     $list = $query->all();
     return ['list' => $list, 'total' => $total];
 }

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

 public function ur_l()
 {
     $db = new Query();
     if ($_POST['search']['value'] != "") {
         $db->where(['like', 'name', $_POST['search']['value']]);
         $db->orWhere(['like', 'address', $_POST['search']['value']]);
         $db->orWhere(['like', 'inn', $_POST['search']['value']]);
         $db->orWhere(['like', 'wabc', $_POST['search']['value']]);
     }
     if (is_numeric($_POST['region'])) {
         $db->InnerJoin('region_ur_l', 'id_ur = ur_l.id');
         $db->andWhere(['=', 'id_reg', $_POST['region']]);
     }
     if ($_POST['pharmopeka']) {
         $db->andWhere(['=', 'ur_l.farmopeka', '1']);
     }
     if ($_POST['order'][0]['column']) {
         $column_order = $_POST['order'][0]['column'];
         $column = $_POST['columns'][$column_order]['data'];
         $db->orderBy($_POST['columns'][$column_order]['data'] . " " . $_POST['order'][0]['dir']);
     }
     if (Yii::$app->user->identity->status == 2) {
         //Регионалы
         $db->andFilterWhere(['=', 'ur_l.regional_id', Yii::$app->user->identity->id]);
     }
     if (Yii::$app->user->identity->status == 3) {
         //Провизоры
         $db->andFilterWhere(['=', 'ur_l.pi_id', Yii::$app->user->identity->id]);
     }
     $db->from('ur_l');
     $db->select(['ur_l.name', 'ur_l.id', 'ur_l.inn', 'ur_l.address', 'ur_l.wabc']);
     $db->limit(10);
     $db->offset(intval(Yii::$app->request->post('start')));
     $data = $db->all();
     $count = $db->count();
     for ($i = 0; $i < count($data); $i++) {
         $data[$i]['name'] = "<a href=\"/ur/update?id=" . $data[$i][id] . "\">" . $data[$i][name] . "</a>";
         $data[$i]['id'] = "<a href=\"/ur/update?id=" . $data[$i][id] . "\">" . $data[$i][id] . "</a>";
     }
     $array = array('draw' => intval(Yii::$app->request->post('draw')), 'recordsTotal' => $count, 'recordsFiltered' => $count, 'data' => $data);
     return $array;
 }

 private function getWhereSearch($filters)
 {
     $query = new Query();
     $query->select(["SDP_TD03_DENPYO.*, TO_CHAR(D03_UPD_DATE, 'YYYY/mm/DD') as CHAR_D03_UPD_DATE,\n            SDP_TD01_CUSTOMER.D01_CUST_NAMEN, SDP_TD01_CUSTOMER.D01_CUST_NAMEK, SDP_TD01_CUSTOMER.D01_NOTE"])->from(static::tableName())->leftJoin('SDP_TD01_CUSTOMER', 'SDP_TD03_DENPYO.D03_CUST_NO = SDP_TD01_CUSTOMER.D01_CUST_NO')->orderBy('D03_DEN_NO');
     if (isset($filters['status']) && $filters['status'] != '') {
         if ($filters['status'] == 1) {
             $query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 0]);
             $query->andWhere(['>', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', date('Ymd')]);
         }
         if ($filters['status'] == 2) {
             $query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 1]);
         }
         if ($filters['status'] == 0) {
             $query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 0]);
             $query->andWhere(['<=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', date('Ymd')]);
         }
     }
     if (isset($filters['car']) && $filters['car']) {
         $query->andwhere(['like', 'SDP_TD03_DENPYO.D03_CAR_NO', $filters['car']]);
     }
     if (isset($filters['job']) && $filters['job'] != '') {
         $denpyo_sagyo = new Sdptd04denpyosagyo();
         $data = $denpyo_sagyo->getData(['D04_SAGYO_NO' => $filters['job']]);
         $arr_den_no = [-1];
         foreach ($data as $k => $v) {
             $arr_den_no[] = $v['D04_DEN_NO'];
         }
         $query->andwhere(['in', 'SDP_TD03_DENPYO.D03_DEN_NO', $arr_den_no]);
     }
     if (isset($filters['start_time']) && $filters['start_time']) {
         $query->andwhere(['>=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', $filters['start_time']]);
     }
     if (isset($filters['end_time']) && $filters['end_time']) {
         $query->andwhere(['<=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', $filters['end_time']]);
     }
     if (isset($filters['detail_no'])) {
         $query->andwhere('SDP_TD03_DENPYO.D03_DEN_NO=:den_no', [':den_no' => $filters['detail_no']]);
     }
     if (isset($filters['m50_ss_cd'])) {
         $query->andwhere('SDP_TD03_DENPYO.D03_SS_CD =:m50_ss_cd', [':m50_ss_cd' => $filters['m50_ss_cd']]);
     }
     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 buildContentQuery($tableName, $other = [], $where = null)
 {
     $query = new Query();
     if (isset($other['fields'])) {
         $query->select($other['fields']);
     }
     if (empty($tableName)) {
         // todo
         $tableName = 'model_news';
     }
     $query->from($tableName);
     if ($where !== null) {
         $query->andWhere($where);
     }
     if (isset($other['where'])) {
         $query->andWhere($other['where']);
     }
     if (isset($other['att1']) && is_integer($other['att1'])) {
         $query->andWhere(['att1' => $other['att1']]);
     }
     if (isset($other['att2']) && is_integer($other['att2'])) {
         $query->andWhere(['att2' => $other['att2']]);
     }
     if (isset($other['att3']) && is_integer($other['att3'])) {
         $query->andWhere(['att3' => $other['att3']]);
     }
     if (isset($other['flag'])) {
         $flagValue = CommonUtility::getFlagValue($other['flag']);
         if ($flagValue > 0) {
             $query->andWhere('flag&' . $flagValue . '>0');
         }
     }
     if (isset($other['is_pic']) && $other['is_pic']) {
         $query->andWhere(['is_pic' => 1]);
     }
     if (isset($other['order'])) {
         $query->orderBy($other['order']);
     } else {
         $query->orderBy('publish_time desc');
     }
     if (isset($other['offset'])) {
         $query->offset(intval($other['offset']));
     } else {
         $query->offset(0);
     }
     if (isset($other['limit'])) {
         $query->limit(intval($other['limit']));
     } else {
         $query->limit(10);
     }
     return $query;
 }

 public function actionIndex()
 {
     Yii::$app->response->format = Response::FORMAT_JSON;
     $params = Yii::$app->getRequest();
     $filter = array();
     $sort = "";
     $page = isset($params->page) ? $params->page : 1;
     $limit = isset($params->limit) ? $params->limit : 10;
     $offset = $limit * ($page - 1);
     /* Filter elements */
     if (isset($params->filter)) {
         $filter = (array) json_decode($params->filter);
     }
     if (isset($params->sort)) {
         $sort = $params->sort;
         if (isset($params->order)) {
             if ($params->order == "false") {
                 $sort .= " desc";
             } else {
                 $sort .= " asc";
             }
         }
     }
     $query = new Query();
     $query->offset($offset)->limit($limit)->from('restaurants')->orderBy($sort)->select("restaurant_id, name, street, house_nr, flat_nr, zip_code, city");
     $command = $query->createCommand();
     $models = $command->queryAll();
     $totalItems = $query->count();
     echo json_encode(array('status' => 1, 'code' => 200, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT);
 }