PHP Query::each方法代码示例

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

 /**
  * Displays all matches and a possibility to register or not.
  * @return mixed Match view
  */
 public function actionIndex()
 {
     $registrationSearchModel = new RegistrationSearch();
     $registrationDataProvider = $registrationSearchModel->search(Yii::$app->request->queryParams);
     if (!($golfer = Golfer::me())) {
         throw new NotFoundHttpException('You are not a golfer.');
     }
     $registrationDataProvider->query->andWhere(['golfer_id' => $golfer->id]);
     $now = date('Y-m-d H:i:s');
     // single matches (not part of a tournament)
     $matchesSearchModel = new MatchSearch();
     $matchesDataProvider = $matchesSearchModel->search(Yii::$app->request->queryParams);
     $matchesDataProvider->query->andWhere(['parent_id' => null])->andWhere(['>', 'start_date', $now]);
     // single tournaments not part of a season
     /*
     select c.id as tournament_id, count(c.id) as tot_count from competition c, competition m
     where c.competition_type = 'TOURNAMENT'
     and m.competition_type = 'MATCH'
     and m.parent_id = c.id
     and c.status = 'OPEN'
     group by c.id
     having tot_count = 1
     */
     $q = new Query();
     $q->select(['c.id as competition_id', 'count(c.id) as tot_count'])->from('competition c, competition m')->andWhere(['c.parent_id' => null])->andWhere(['c.competition_type' => Competition::TYPE_TOURNAMENT])->andWhere(['m.competition_type' => Competition::TYPE_MATCH])->andWhere('m.parent_id = c.id')->andWhere(['c.status' => Competition::STATUS_OPEN])->andWhere(['m.status' => Competition::STATUS_OPEN])->andWhere(['>', 'm.start_date', $now])->groupBy('c.id');
     $tournament_ids = [];
     foreach ($q->each() as $tournament) {
         $tournament_ids[] = $tournament['competition_id'];
     }
     $tournamentsSearchModel = new TournamentSearch();
     $tournamentsDataProvider = $tournamentsSearchModel->search(Yii::$app->request->queryParams);
     $tournamentsDataProvider->query->andWhere(['id' => $tournament_ids]);
     // seasons
     $seasonsSearchModel = new SeasonSearch();
     $seasonsDataProvider = $seasonsSearchModel->search(Yii::$app->request->queryParams);
     $seasonsDataProvider->query->andWhere(['status' => Competition::STATUS_OPEN]);
     return $this->render('index', ['registrationSearchModel' => $registrationSearchModel, 'registrationDataProvider' => $registrationDataProvider, 'matchesSearchModel' => $matchesSearchModel, 'matchesDataProvider' => $matchesDataProvider, 'tournamentsSearchModel' => $tournamentsSearchModel, 'tournamentsDataProvider' => $tournamentsDataProvider, 'seasonsSearchModel' => $seasonsSearchModel, 'seasonsDataProvider' => $seasonsDataProvider]);
 }

 public function testQuery()
 {
     $db = $this->getConnection();
     // initialize property test
     $query = new Query();
     $query->from('customer')->orderBy('id');
     $result = $query->batch(2, $db);
     $this->assertTrue($result instanceof BatchQueryResult);
     $this->assertEquals(2, $result->batchSize);
     $this->assertTrue($result->query === $query);
     // normal query
     $query = new Query();
     $query->from('customer')->orderBy('id');
     $allRows = [];
     $batch = $query->batch(2, $db);
     foreach ($batch as $rows) {
         $allRows = array_merge($allRows, $rows);
     }
     $this->assertEquals(3, count($allRows));
     $this->assertEquals('user1', $allRows[0]['name']);
     $this->assertEquals('user2', $allRows[1]['name']);
     $this->assertEquals('user3', $allRows[2]['name']);
     // rewind
     $allRows = [];
     foreach ($batch as $rows) {
         $allRows = array_merge($allRows, $rows);
     }
     $this->assertEquals(3, count($allRows));
     // reset
     $batch->reset();
     // empty query
     $query = new Query();
     $query->from('customer')->where(['id' => 100]);
     $allRows = [];
     $batch = $query->batch(2, $db);
     foreach ($batch as $rows) {
         $allRows = array_merge($allRows, $rows);
     }
     $this->assertEquals(0, count($allRows));
     // query with index
     $query = new Query();
     $query->from('customer')->indexBy('name');
     $allRows = [];
     foreach ($query->batch(2, $db) as $rows) {
         $allRows = array_merge($allRows, $rows);
     }
     $this->assertEquals(3, count($allRows));
     $this->assertEquals('address1', $allRows['user1']['address']);
     $this->assertEquals('address2', $allRows['user2']['address']);
     $this->assertEquals('address3', $allRows['user3']['address']);
     // each
     $query = new Query();
     $query->from('customer')->orderBy('id');
     $allRows = [];
     foreach ($query->each(100, $db) as $rows) {
         $allRows[] = $rows;
     }
     $this->assertEquals(3, count($allRows));
     $this->assertEquals('user1', $allRows[0]['name']);
     $this->assertEquals('user2', $allRows[1]['name']);
     $this->assertEquals('user3', $allRows[2]['name']);
     // each with key
     $query = new Query();
     $query->from('customer')->orderBy('id')->indexBy('name');
     $allRows = [];
     foreach ($query->each(100, $db) as $key => $row) {
         $allRows[$key] = $row;
     }
     $this->assertEquals(3, count($allRows));
     $this->assertEquals('address1', $allRows['user1']['address']);
     $this->assertEquals('address2', $allRows['user2']['address']);
     $this->assertEquals('address3', $allRows['user3']['address']);
 }

 function actionI($id)
 {
     $name = '';
     $query = new Query();
     $query->select(['id', 'id_papka', 'title'])->from('pages')->orderBy(['id' => SORT_DESC])->where(['id' => $id, 'status' => '1'])->limit(20);
     foreach ($query->each() as $rowname) {
         $name = $rowname['id_papka'];
     }
     $dataProvider = new ActiveDataProvider(['query' => Pages::find()->where(['id' => $id, 'status' => '1', 'id_papka' => $name]), 'pagination' => ['pageSize' => 1, 'pageParam' => 'i']]);
     return $this->render('i', ['dataProvider' => $dataProvider]);
 }

 public function actionRefreshFanHeadimgurl($id = null)
 {
     $id = empty($id) ? 0 : $id;
     $gh_id = Yii::$app->wx->getGhid();
     $db = \Yii::$app->db;
     $query = new Query();
     $tableName = MUser::tableName();
     $query->from($tableName)->where('id > :id', [':id' => $id])->orderBy(['id' => SORT_ASC]);
     $i = 0;
     foreach ($query->each() as $user) {
         if (empty($user['subscribe'])) {
             continue;
         }
         U::W(["refresh", $user]);
         Yii::$app->wx->setGhId($user['gh_id']);
         $arr = Yii::$app->wx->WxGetUserInfo($user['openid']);
         U::W($arr);
         if ($arr['subscribe'] == 0) {
             $n = $db->createCommand()->update($tableName, ['subscribe' => 0], 'id = :id', [':id' => $user['id']])->execute();
         } else {
             $n = $db->createCommand()->update($tableName, ['nickname' => $arr['nickname'], 'headimgurl' => $arr['headimgurl'], 'city' => empty($arr['city']) ? '' : $arr['city'], 'province' => empty($arr['province']) ? '' : $arr['province'], 'country' => empty($arr['country']) ? '' : $arr['country'], 'sex' => empty($arr['sex']) ? '' : $arr['sex'], 'subscribe' => empty($arr['subscribe']) ? '' : $arr['subscribe']], 'id = :id', [':id' => $user['id']])->execute();
         }
         U::W("id={$user['id']}");
         $i++;
         if ($i % 1000 == 1) {
             U::W($i);
         }
     }
 }