This method is similar to Query::batch except that in each iteration of the result,
only one row of data is returned. For example,
php
$query = (new Query)->from('user');
foreach ($query->each() as $row) {
}
public each ( integer $batchSize = 100, |
||
$batchSize | integer | the number of records to be fetched in each batch. |
$db | the database connection. If not set, the "db" application component will be used. | |
리턴 | the batch query result. It implements the [[\Iterator]] interface and can be traversed to retrieve the data in batches. |
/** * 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); } } }