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