/**
* @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());
}
/**
* expressions in WHERE
*
* @covers Phossa\Query\Dialect\Common::select()
*/
public function testSelect15()
{
$str = <<<EOT
SELECT
"id"
FROM
"students"
WHERE
("name" = 'Thomas' OR "age" > 18)
EOT;
$this->assertEquals(preg_replace("/\r\n/", "\n", $str), $this->builder->select()->field("id")->from("students")->where($this->builder->expr()->andWhere("name", "Thomas")->orWhere("age", ">", 18))->getStatement($this->settings));
}
/**
* example16: expr
*
* @covers Phossa\Query\Builder::expr()
*/
public function testReadme16()
{
// builder object
$builder = new Builder();
// 01: where
$str = <<<EOT
SELECT
*
FROM
"Users"
WHERE
("age" < 18 OR "gender" = 'female')
OR ("age" > 60 OR ("age" > 55 AND "gender" = 'female'))
EOT;
$this->assertEquals(preg_replace("/\r\n/", "\n", $str), $builder->select()->from('Users')->where($builder->expr()->where('age', '<', 18)->orWhere('gender', 'female'))->orWhere($builder->expr()->where('age', '>', 60)->orWhere($builder->expr()->where('age', '>', 55)->where('gender', 'female')))->getStatement($this->settings));
}
