public function test_limits_and_offsets() { $DB = $this->tdb; $dbman = $DB->get_manager(); if (false) { $DB = new moodle_database(); } $table = $this->get_test_table(); $tablename = $table->getName(); $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); $table->add_field('content', XMLDB_TYPE_TEXT, 'big', XMLDB_UNSIGNED, XMLDB_NOTNULL); $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); $dbman->create_table($table); $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'a', 'content' => 'one'))); $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'b', 'content' => 'two'))); $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'c', 'content' => 'three'))); $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'd', 'content' => 'four'))); $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'e', 'content' => 'five'))); $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'f', 'content' => 'six'))); $sqlqm = "SELECT *\n FROM {{$tablename}}"; $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4)); $this->assertEquals(2, count($records)); $this->assertEquals('e', reset($records)->name); $this->assertEquals('f', end($records)->name); $sqlqm = "SELECT *\n FROM {{$tablename}}"; $this->assertEmpty($records = $DB->get_records_sql($sqlqm, null, 8)); $sqlqm = "SELECT *\n FROM {{$tablename}}"; $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 4)); $this->assertEquals(4, count($records)); $this->assertEquals('a', reset($records)->name); $this->assertEquals('d', end($records)->name); $sqlqm = "SELECT *\n FROM {{$tablename}}"; $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 8)); $this->assertEquals(6, count($records)); $this->assertEquals('a', reset($records)->name); $this->assertEquals('f', end($records)->name); $sqlqm = "SELECT *\n FROM {{$tablename}}"; $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 1, 4)); $this->assertEquals(4, count($records)); $this->assertEquals('b', reset($records)->name); $this->assertEquals('e', end($records)->name); $sqlqm = "SELECT *\n FROM {{$tablename}}"; $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4)); $this->assertEquals(2, count($records)); $this->assertEquals('e', reset($records)->name); $this->assertEquals('f', end($records)->name); $sqlqm = "SELECT t.*, t.name AS test\n FROM {{$tablename}} t\n ORDER BY t.id ASC"; $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4)); $this->assertEquals(2, count($records)); $this->assertEquals('e', reset($records)->name); $this->assertEquals('f', end($records)->name); $sqlqm = "SELECT DISTINCT t.name, t.name AS test\n FROM {{$tablename}} t\n ORDER BY t.name DESC"; $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 4, 4)); $this->assertEquals(2, count($records)); $this->assertEquals('b', reset($records)->name); $this->assertEquals('a', end($records)->name); $sqlqm = "SELECT 1\n FROM {{$tablename}} t\n WHERE t.name = 'a'"; $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 1)); $this->assertEquals(1, count($records)); $sqlqm = "SELECT 'constant'\n FROM {{$tablename}} t\n WHERE t.name = 'a'"; $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 8)); $this->assertEquals(1, count($records)); $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'a', 'content' => 'one'))); $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'b', 'content' => 'two'))); $this->assertNotEmpty($DB->insert_record($tablename, array('name' => 'c', 'content' => 'three'))); $sqlqm = "SELECT t.name, COUNT(DISTINCT t2.id) AS count, 'Test' AS teststring\n FROM {{$tablename}} t\n LEFT JOIN (\n SELECT t.id, t.name\n FROM {{$tablename}} t\n ) t2 ON t2.name = t.name\n GROUP BY t.name\n ORDER BY t.name ASC"; $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm)); $this->assertEquals(6, count($records)); // a,b,c,d,e,f $this->assertEquals(2, reset($records)->count); // a has 2 records now $this->assertEquals(1, end($records)->count); // f has 1 record still $this->assertNotEmpty($records = $DB->get_records_sql($sqlqm, null, 0, 2)); $this->assertEquals(2, count($records)); $this->assertEquals(2, reset($records)->count); $this->assertEquals(2, end($records)->count); }