public function testOrderRandom()
{
$builder = new BaseBuilder('user', $this->db);
$builder->orderBy('name', 'random');
$expectedSQL = "SELECT * FROM \"user\" ORDER BY RAND()";
$this->assertEquals($expectedSQL, str_replace("\n", ' ', $builder->getCompiledSelect()));
}
public function testOrNotGroups()
{
$builder = new BaseBuilder('user', $this->db);
$builder->where('name', 'Darth')->orNotGroupStart()->where('id >', 3)->where('name !=', 'Luke')->groupEnd();
$expectedSQL = "SELECT * FROM \"user\" WHERE \"name\" = :name OR NOT ( \"id\" > :id AND \"name\" != :name0 )";
$this->assertEquals($expectedSQL, str_replace("\n", ' ', $builder->getCompiledSelect()));
}
public function testJoinMultipleConditions()
{
$builder = new BaseBuilder('table1', $this->db);
$builder->join('table2', "table1.field1 = table2.field2 AND table1.field1 = 'foo' AND table2.field2 = 0", 'LEFT');
$expectedSQL = "SELECT * FROM \"table1\" LEFT JOIN \"table2\" ON \"table1\".\"field1\" = \"table2\".\"field2\" AND \"table1\".\"field1\" = 'foo' AND \"table2\".\"field2\" = 0";
$this->assertEquals($expectedSQL, str_replace("\n", ' ', $builder->getCompiledSelect()));
}
public function testDelete()
{
$builder = new BaseBuilder('user', $this->db);
$builder->select('country')->distinct();
$expectedSQL = "SELECT DISTINCT \"country\" FROM \"user\"";
$this->assertEquals($expectedSQL, str_replace("\n", ' ', $builder->getCompiledSelect()));
}
public function testFromWithMultipleTablesAsString()
{
$builder = new BaseBuilder('user', $this->db);
$builder->from(['jobs, roles']);
$expectedSQL = "SELECT * FROM \"user\", \"jobs\", \"roles\"";
$this->assertEquals($expectedSQL, str_replace("\n", ' ', $builder->getCompiledSelect()));
}
public function testLimitAndOffsetMethod()
{
$builder = new BaseBuilder('user', $this->db);
$builder->limit(5)->offset(1);
$expectedSQL = "SELECT * FROM \"user\" LIMIT 1, 5";
$this->assertEquals($expectedSQL, str_replace("\n", ' ', $builder->getCompiledSelect()));
}
public function testCountAllResults()
{
$builder = new BaseBuilder('jobs', $this->db);
$answer = $builder->where('id >', 3)->countAllResults(null, true);
$expectedSQL = "SELECT COUNT(*) AS \"numrows\" FROM \"jobs\" WHERE \"id\" > :id";
$this->assertEquals($expectedSQL, str_replace("\n", ' ', $answer));
}
public function testEmptyWithNoTable()
{
$builder = new BaseBuilder('jobs', $this->db);
$builder->returnDeleteSQL = true;
$answer = $builder->emptyTable(true);
$expectedSQL = "DELETE FROM \"jobs\"";
$this->assertEquals($expectedSQL, str_replace("\n", ' ', $answer));
}
public function testOrNotLike()
{
$builder = new BaseBuilder('job', $this->db);
$builder->like('name', 'veloper')->orNotLike('name', 'ian');
$expectedSQL = "SELECT * FROM \"job\" WHERE \"name\" LIKE :name ESCAPE '!' OR \"name\" NOT LIKE :name0 ESCAPE '!'";
$expectedBinds = ['name' => '%veloper%', 'name0' => '%ian%'];
$this->assertEquals($expectedSQL, str_replace("\n", ' ', $builder->getCompiledSelect()));
$this->assertSame($expectedBinds, $builder->getBinds());
}
public function testTruncate()
{
$builder = new BaseBuilder('user', $this->db);
$expectedSQL = "TRUNCATE \"user\"";
$this->assertEquals($expectedSQL, $builder->truncate(true));
}
public function testSelectMaxWithDotNameAndNoAlias()
{
$builder = new BaseBuilder('invoices', $this->db);
$builder->selectMax('db.payments');
$expected = "SELECT MAX(\"db\".\"payments\") AS \"payments\" FROM \"invoices\"";
$this->assertEquals($expected, str_replace("\n", ' ', $builder->getCompiledSelect()));
}
public function testUpdateBatchThrowsExceptionWithEmptySetArray()
{
$builder = new BaseBuilder('jobs', $this->db);
$this->setExpectedException('CodeIgniter\\DatabaseException', 'updateBatch() called with no data');
$builder->updateBatch([], 'id');
}
/**
* Delete statement
*
* Generates a platform-specific delete string from the supplied data
*
* @param string the table name
*
* @return string
*/
protected function _delete($table)
{
$this->QBLimit = false;
return parent::_delete($table);
}
