A batch query supports fetching data in batches, which can keep the memory usage under a limit.
This method will return a BatchQueryResult object which implements the [[\Iterator]] interface
and can be traversed to retrieve the data in batches.
For example,
php
$query = (new Query)->from('user');
foreach ($query->batch() as $rows) {
$rows is an array of 100 or fewer rows from user table
}
public batch ( 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. | |
return | the batch query result. It implements the [[\Iterator]] interface and can be traversed to retrieve the data in batches. |
/** * Perform a Database Query and show the result as CSV * * By default, write to the output buffer. * But, if you set the filename, the file will be downloadable. * * @param string $filename Name of the file to download */ public function performAsCSV($filename = null) { // Create the CSV into memory $csv = Writer::createFromFileObject(new SplTempFileObject()); // Insert fields names as the CSV header $csv->insertOne($this->getCSVHeader()); // Print to the output stream foreach ($this->query->batch() as $items) { // $items is an array of 100 or fewer rows from the db table $csv->insertAll($items); } $csv->output($filename); }
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']); }