In some databases, this operation might not be supported or will require
the query to be transformed in order to limit the result set size.
### Examples
$query->limit(10) // generates LIMIT 10
$query->limit($query->newExpr()->add(['1 + 1'])); // LIMIT (1 + 1)
public function findCursor(Query $query) { $current = $this->request->query('cursor'); $limit = $this->request->query('limit') ?: 10; if ($current) { $query->where(['id >' => $current]); } $query->limit($limit); return $query; }
/** * Tests selecting rows combining a limit and offset clause * * @return void */ public function testSelectOffset() { $query = new Query($this->connection); $result = $query->select('id')->from('comments')->limit(1)->offset(0)->order(['id' => 'ASC'])->execute(); $this->assertCount(1, $result); $this->assertEquals(['id' => 1], $result->fetch('assoc')); $query = new Query($this->connection); $result = $query->select('id')->from('comments')->limit(1)->offset(1)->execute(); $this->assertCount(1, $result); $this->assertEquals(['id' => 2], $result->fetch('assoc')); $query = new Query($this->connection); $result = $query->select('id')->from('comments')->limit(1)->offset(2)->execute(); $this->assertCount(1, $result); $this->assertEquals(['id' => 3], $result->fetch('assoc')); $query = new Query($this->connection); $result = $query->select('id')->from('articles')->order(['id' => 'DESC'])->limit(1)->offset(0)->execute(); $this->assertCount(1, $result); $this->assertEquals(['id' => 3], $result->fetch('assoc')); $result = $query->limit(2)->offset(1)->execute(); $this->assertCount(2, $result); $this->assertEquals(['id' => 2], $result->fetch('assoc')); $this->assertEquals(['id' => 1], $result->fetch('assoc')); }
/** * Tests selecting rows combining a limit and offset clause * * @return void */ public function testSelectOffset() { $this->loadFixtures('Articles', 'Comments'); $query = new Query($this->connection); $result = $query->select('id')->from('comments')->limit(1)->offset(0)->order(['id' => 'ASC'])->execute(); $this->assertCount(1, $result); $this->assertEquals(['id' => 1], $result->fetch('assoc')); $query = new Query($this->connection); $result = $query->select('id')->from('comments')->limit(1)->offset(1)->execute(); $this->assertCount(1, $result); $this->assertEquals(['id' => 2], $result->fetch('assoc')); $query = new Query($this->connection); $result = $query->select('id')->from('comments')->limit(1)->offset(2)->execute(); $this->assertCount(1, $result); $this->assertEquals(['id' => 3], $result->fetch('assoc')); $query = new Query($this->connection); $result = $query->select('id')->from('articles')->order(['id' => 'DESC'])->limit(1)->offset(0)->execute(); $this->assertCount(1, $result); $this->assertEquals(['id' => 3], $result->fetch('assoc')); $result = $query->limit(2)->offset(1)->execute(); $this->assertCount(2, $result); $this->assertEquals(['id' => 2], $result->fetch('assoc')); $this->assertEquals(['id' => 1], $result->fetch('assoc')); $query = new Query($this->connection); $query->select('id')->from('comments')->limit(1)->offset(1)->execute(); $dirty = $this->readAttribute($query, '_dirty'); $this->assertFalse($dirty); $query->offset(2); $dirty = $this->readAttribute($query, '_dirty'); $this->assertTrue($dirty); }