/**
* @covers Phossa\Query\Clause\UnionTrait::union
* @covers Phossa\Query\Clause\UnionTrait::unionAll
*/
public function testUnion()
{
$ousers = $this->builder->table('oldusers');
$this->assertEquals('SELECT * FROM "users" UNION SELECT * FROM "oldusers"', $this->builder->select()->union()->select()->table('oldusers')->getStatement());
// multiple union
$this->assertEquals('SELECT * FROM "users" UNION SELECT * FROM "oldusers" UNION ALL SELECT "user_id" FROM "oldusers"', $this->builder->select()->union()->select()->from('oldusers')->unionAll()->select('user_id')->from('oldusers')->getStatement());
}
/**
* @covers Phossa\Query\Clause\LimitTrait::page
*/
public function testPage()
{
// page starts with 1
$this->assertEquals('SELECT * FROM "users" LIMIT 30 OFFSET 30', $this->builder->select()->page(2)->getStatement());
// change page length
$this->assertEquals('SELECT * FROM "users" LIMIT 10 OFFSET 20', $this->builder->select()->page(3, 10)->getStatement());
}
/**
* flags & for update
*
* @covers Phossa\Query\Dialect\Mysql::select()
*/
public function testSelect02()
{
$str = <<<EOT
SELECT
HIGH_PRIORITY SQL_CACHE
`name`
FROM
`students`
FOR UPDATE
EOT;
$this->assertEquals(preg_replace("/\r\n/", "\n", $str), $this->builder->select()->col("name")->from("students")->addHint('HIGH_PRIORITY')->addHint('SQL_CACHE')->forUpdate()->getStatement());
}
/**
* pass parameters
*
* @covers Phossa\Query\Dialect\Common::select()
*/
public function testSelect20()
{
$str = 'SELECT * FROM "students" WHERE "age" IN RANGE(?, ?)';
$sel = $this->builder->select()->from("students")->where("age", "IN", $this->builder->raw('RANGE(?, ?)', 1, 1.2));
$this->assertEquals(preg_replace("/\r\n/", "\n", $str), $sel->getStatement(['positionedParam' => true]));
$this->assertEquals([1, 1.2], $sel->getBindings());
}
/**
* @covers Phossa\Query\Clause\WhereTrait::whereExists
* @covers Phossa\Query\Clause\WhereTrait::orWhereExists
* @covers Phossa\Query\Clause\WhereTrait::whereNotExists
* @covers Phossa\Query\Clause\WhereTrait::orWhereNotExists
*/
public function testWhereExists()
{
$users = $this->builder->select('user_id')->where('age', '>', 60);
$sales = $this->builder->table('sales');
// whereExists
$this->assertEquals('SELECT * FROM "sales" WHERE EXISTS (SELECT "user_id" FROM "users" WHERE "age" > 60)', $sales->select()->whereExists($users)->getStatement());
// whereNotExists
$this->assertEquals('SELECT * FROM "sales" WHERE NOT EXISTS (SELECT "user_id" FROM "users" WHERE "age" > 60)', $sales->select()->whereNotExists($users)->getStatement());
// orWhereExists
$this->assertEquals('SELECT * FROM "sales" WHERE "order_id" > 10 OR EXISTS (SELECT "user_id" FROM "users" WHERE "age" > 60)', $sales->select()->where('order_id', '>', 10)->orWhereExists($users)->getStatement());
// orWhereNotExists
$this->assertEquals('SELECT * FROM "sales" WHERE "order_id" > 10 OR NOT EXISTS (SELECT "user_id" FROM "users" WHERE "age" > 60)', $sales->select()->where('order_id', '>', 10)->orWhereNotExists($users)->getStatement());
}
/**
* @covers Phossa\Query\Clause\BeforeAfterTrait::after
*/
public function testAfter()
{
$this->assertEquals('SELECT * FROM "users" PARTITION p1', $this->builder->select()->after('from', 'PARTITION p1')->getStatement());
}
/**
* @covers Phossa\Query\Clause\ColTrait::distinct
*/
public function testDistinct()
{
$this->assertEquals('SELECT DISTINCT "user_name"', $this->builder->select()->distinct()->col('user_name')->getStatement());
$this->assertEquals('SELECT DISTINCT "user_name" AS "n"', $this->builder->select()->distinct()->col('user_name', 'n')->getStatement());
}
/**
* example18: parameters
*
* @covers Phossa\Query\Dialect\Common::select()
*/
public function testReadme18()
{
// builder object
$users = new Builder(new Common(), 'users');
// 01: positioned parameters
$this->assertEquals('SELECT * FROM "users" WHERE "user_id" = ?', $users->select()->where("user_id", 10)->getPositionedStatement());
// 02: named parameters
$this->assertEquals('SELECT * FROM "users" WHERE "user_name" = :name AND "user_id" > 100', $users->select()->where("user_name", ':name')->andWhere('user_id', '>', 100)->getNamedStatement());
}
/**
* table() is same as from()
*
* @covers Phossa\Query\Clause\FromTrait::table
*/
public function testTable()
{
$this->assertEquals('SELECT * FROM "users" AS "u", "items" AS "i"', $this->builder->select()->table(['users' => 'u', 'items' => 'i'])->getStatement());
}
/**
* @covers Phossa\Query\Clause\HavingTrait::havingRaw
* @covers Phossa\Query\Clause\HavingTrait::orHavingRaw
*/
public function testHavingRaw()
{
$this->assertEquals('SELECT * FROM "users" HAVING age = 10 OR level > 10', $this->builder->select()->havingRaw('age = 10')->orHavingRaw('level > 10')->getStatement());
}
/**
* @covers Phossa\Query\Clause\OrderByTrait::orderByRaw
*/
public function testOrderByRaw()
{
$this->assertEquals('SELECT * FROM "users" ORDER BY age ASC, level DESC', $this->builder->select()->orderByRaw('age ASC, level DESC')->getStatement());
}
/**
* Test function combinations
*
* @covers Phossa\Query\Clause\FunctionTrait::func
*/
public function testFunc2()
{
$this->assertEquals('SELECT COUNT("user_id") AS "cnt", MIN("age"), MAX("score")', $this->builder->select()->count('user_id', 'cnt')->min('age')->max('score')->getStatement());
$this->assertEquals('SELECT SUM(DISTINCT "age") AS "a", "score"', $this->builder->select()->sumDistinct('age', 'a')->col('score')->getStatement());
}
/**
* raw mode
*
* @covers Phossa\Query\Clause\JoinTrait::crossJoin
*/
public function testJoinRaw()
{
$this->assertEquals('SELECT * FROM "users" CROSS JOIN sales s ON users.uid = s.uid', $this->builder->select()->crossJoin('sales s ON users.uid = s.uid')->getStatement());
}
