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 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 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 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 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 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 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()));
}
