/**
* @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\JoinTrait::realJoin
*/
public function testRealJoin()
{
// one col
$this->assertEquals('SELECT * FROM "users" INNER JOIN "sales" ON "users"."uid" = "sales"."uid"', $this->builder->select()->realJoin('INNER', 'sales', 'uid')->getStatement());
// 2 cols
$this->assertEquals('SELECT * FROM "users" INNER JOIN "sales" ON "users"."uid" = "sales"."s_uid"', $this->builder->select()->realJoin('INNER', 'sales', 'uid', 's_uid')->getStatement());
// 2 cols with =
$this->assertEquals('SELECT * FROM "users" INNER JOIN "sales" ON "users"."uid" = "sales"."s_uid"', $this->builder->select()->realJoin('INNER', 'sales', 'uid', '=', 's_uid')->getStatement());
// test table alias
$this->assertEquals('SELECT * FROM "users" INNER JOIN "sales" AS "s" ON "users"."uid" = "s"."uid"', $this->builder->select()->realJoin('INNER', 'sales s', 'uid', '=', 'uid')->getStatement());
// join with subquery
$this->assertEquals('SELECT * FROM "users" INNER JOIN (SELECT "uid" FROM "oldusers") AS "x" ON "users"."uid" = "x"."uid"', $this->builder->select()->realJoin('INNER', $this->builder->table('oldusers')->select('uid')->alias('x'), 'uid')->getStatement());
// multiple joins
$this->assertEquals('SELECT * FROM "users" INNER JOIN "sales" AS "s" ON "users"."uid" = "s"."uid" INNER JOIN "order" AS "o" ON "users"."uid" = "o"."uid"', $this->builder->select()->realJoin('INNER', 'sales s', 'uid', '=', 'uid')->realJoin('INNER', 'order o', 'uid')->getStatement());
// grouped on
$this->assertEquals('SELECT * FROM "users" INNER JOIN "sales" (ON "users"."uid" = "sales"."s_uid" OR "users"."uid" = "sales"."puid")', $this->builder->select()->realJoin('INNER', 'sales', $this->builder->expr()->on('users.uid', 'sales.s_uid')->orOn('users.uid', 'sales.puid'))->getStatement());
}
/**
* @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());
}
