public function testGroup() { require 'unit-tests/config.db.php'; if (empty($configMysql)) { $this->markTestSkipped("Test skipped"); return; } $di = $this->_getDI(); $builder = new Builder(); $phql = $builder->setDi($di)->columns(array('name', 'SUM(price)'))->from('Robots')->groupBy('id, name')->getPhql(); $this->assertEquals($phql, 'SELECT name, SUM(price) FROM [Robots] GROUP BY [id], [name]'); }
public function testAction() { $di = $this->_getDI(); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots]'); $builder = new Builder(); $phql = $builder->setDi($di)->from(array('Robots', 'RobotsParts'))->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].*, [RobotsParts].* FROM [Robots], [RobotsParts]'); $builder = new Builder(); $phql = $builder->setDi($di)->columns('*')->from('Robots')->getPhql(); $this->assertEquals($phql, 'SELECT * FROM [Robots]'); $builder = new Builder(); $phql = $builder->setDi($di)->columns(array('id', 'name'))->from('Robots')->getPhql(); $this->assertEquals($phql, 'SELECT id, name FROM [Robots]'); $builder = new Builder(); $phql = $builder->setDi($di)->columns('id')->from('Robots')->getPhql(); $this->assertEquals($phql, 'SELECT id FROM [Robots]'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->where('Robots.name = "Voltron"')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] WHERE Robots.name = "Voltron"'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->where('Robots.name = "Voltron"')->andWhere('Robots.id > 100')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] WHERE (Robots.name = "Voltron") AND (Robots.id > 100)'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->where('Robots.name = "Voltron"')->orWhere('Robots.id > 100')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] WHERE (Robots.name = "Voltron") OR (Robots.id > 100)'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->where(100)->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] WHERE [Robots].[id] = 100'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->groupBy('Robots.name')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] GROUP BY Robots.name'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->groupBy(array('Robots.name', 'Robots.id'))->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] GROUP BY Robots.name, Robots.id'); $builder = new Builder(); $phql = $builder->setDi($di)->columns(array('Robots.name', 'SUM(Robots.price)'))->from('Robots')->groupBy('Robots.name')->getPhql(); $this->assertEquals($phql, 'SELECT Robots.name, SUM(Robots.price) FROM [Robots] GROUP BY Robots.name'); $builder = new Builder(); $phql = $builder->setDi($di)->columns(array('Robots.name', 'SUM(Robots.price)'))->from('Robots')->groupBy('Robots.name')->having('SUM(Robots.price) > 1000')->getPhql(); $this->assertEquals($phql, 'SELECT Robots.name, SUM(Robots.price) FROM [Robots] GROUP BY Robots.name HAVING SUM(Robots.price) > 1000'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->join('RobotsParts')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] JOIN [RobotsParts]'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->join('RobotsParts', null, 'p')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] JOIN [RobotsParts] AS [p]'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->join('RobotsParts', 'Robots.id = RobotsParts.robots_id', 'p')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] JOIN [RobotsParts] AS [p] ON Robots.id = RobotsParts.robots_id'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->join('RobotsParts', 'Robots.id = RobotsParts.robots_id', 'p')->join('Parts', 'Parts.id = RobotsParts.parts_id', 't')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] JOIN [RobotsParts] AS [p] ON Robots.id = RobotsParts.robots_id JOIN [Parts] AS [t] ON Parts.id = RobotsParts.parts_id'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->leftJoin('RobotsParts', 'Robots.id = RobotsParts.robots_id')->leftJoin('Parts', 'Parts.id = RobotsParts.parts_id')->where('Robots.id > 0')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] LEFT JOIN [RobotsParts] ON Robots.id = RobotsParts.robots_id LEFT JOIN [Parts] ON Parts.id = RobotsParts.parts_id WHERE Robots.id > 0'); $builder = new Builder(); $phql = $builder->setDi($di)->addFrom('Robots', 'r')->getPhql(); $this->assertEquals($phql, 'SELECT [r].* FROM [Robots] AS [r]'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->addFrom('Parts', 'p')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].*, [p].* FROM [Robots], [Parts] AS [p]'); $builder = new Builder(); $phql = $builder->setDi($di)->from(array('r' => 'Robots'))->addFrom('Parts', 'p')->getPhql(); $this->assertEquals($phql, 'SELECT [r].*, [p].* FROM [Robots] AS [r], [Parts] AS [p]'); $builder = new Builder(); $phql = $builder->setDi($di)->from(array('r' => 'Robots', 'p' => 'Parts'))->getPhql(); $this->assertEquals($phql, 'SELECT [r].*, [p].* FROM [Robots] AS [r], [Parts] AS [p]'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->orderBy('Robots.name')->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] ORDER BY Robots.name'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->orderBy(array(1, 'Robots.name'))->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] ORDER BY 1, Robots.name'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->limit(10)->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] LIMIT 10'); $builder = new Builder(); $phql = $builder->setDi($di)->from('Robots')->limit(10, 5)->getPhql(); $this->assertEquals($phql, 'SELECT [Robots].* FROM [Robots] LIMIT 10 OFFSET 5'); }
public function testSelectDistinctAll() { require PATH_CONFIG . 'config.db.php'; if (empty($configMysql)) { $this->markTestSkipped("Test skipped"); return; } $di = $this->_getDI(); $builder = new Builder(); $phql = $builder->setDi($di)->distinct(true)->columns(array('Robots.name'))->from('Robots')->getPhql(); $this->assertEquals($phql, 'SELECT DISTINCT Robots.name FROM [Robots]'); $builder = new Builder(); $phql = $builder->setDi($di)->distinct(false)->columns(array('Robots.name'))->from('Robots')->getPhql(); $this->assertEquals($phql, 'SELECT ALL Robots.name FROM [Robots]'); $builder = new Builder(); $phql = $builder->setDi($di)->distinct(true)->distinct(null)->columns(array('Robots.name'))->from('Robots')->getPhql(); $this->assertEquals($phql, 'SELECT Robots.name FROM [Robots]'); }
public function testGroup() { $this->specify("Query Builders don't work with a GROUP BY statement", function () { $di = $this->di; $builder = new Builder(); $phql = $builder->setDi($di)->columns(["name", "SUM(price)"])->from(Robots::class)->groupBy("id, name")->getPhql(); expect($phql)->equals("SELECT name, SUM(price) FROM [" . Robots::class . "] GROUP BY [id], [name]"); }); }