/** * @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)); }