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. | |
| Résultat | 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);
}
}
}