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]");
});
}