public function testAssemble()
{
$clause = new LimitClause();
$clause->setLimit(10);
$this->assertEquals('LIMIT 10', QueryAssembler::stringify($clause));
$clause->setOffset(5);
$this->assertEquals('LIMIT 5,10', QueryAssembler::stringify($clause));
$stmt = QueryBuilder::select(AllSelectExpression::create())->limit(3);
$assembler = new QueryAssembler($stmt);
$this->assertEquals('SELECT * LIMIT ?', $assembler->getQuery());
$this->assertEquals([3], $assembler->getParameters());
$stmt = QueryBuilder::select(AllSelectExpression::create())->limitWithOffset(0, 3);
$assembler = new QueryAssembler($stmt);
$this->assertEquals('SELECT * LIMIT ?,?', $assembler->getQuery());
$this->assertEquals([0, 3], $assembler->getParameters());
$offset = NumericExpression::create(0);
$limit = NumericExpression::create(3);
$stmt = QueryBuilder::select(AllSelectExpression::create())->limitWithOffset($offset, $limit);
$assembler = new QueryAssembler($stmt);
$this->assertEquals('SELECT * LIMIT ?,?', $assembler->getQuery());
$this->assertEquals([0, 3], $assembler->getParameters());
$offset->setValue(25);
$limit->setValue(38);
$this->assertEquals('SELECT * LIMIT ?,?', $assembler->getQuery());
$this->assertEquals([25, 38], $assembler->getParameters());
}
public function assembleLimitClause(LimitClause $clause)
{
$assembled = $clause->getAction() . ' ';
if ($clause->hasOffset()) {
$assembled .= $this->_assemblePrepared($clause->getOffset()) ?: $clause->getOffset()->getValue();
$assembled .= ',';
}
$assembled .= $this->_assemblePrepared($clause->getLimit()) ?: $clause->getLimit()->getValue();
return $assembled;
}
public function limitWithOffset($offset, $limit)
{
/**
* @var $this IStatement
*/
$limitClause = new LimitClause();
$limitClause->setLimit($limit);
$limitClause->setOffset($offset);
$this->addClause($limitClause);
return $this;
}
public function testAssemble()
{
$statement = new QueryStatement();
$select = new SelectClause();
$select->addExpression(new AllSelectExpression());
$statement->addClause($select);
$this->assertEquals('SELECT *', QueryAssembler::stringify($statement));
$from = new FromClause();
$from->setTable('tbl');
$statement->addClause($from);
$this->assertEquals('SELECT * FROM tbl', QueryAssembler::stringify($statement));
$where = new WhereClause();
$where->addPredicate((new NotEqualPredicate())->setField('username'));
$statement->addClause($where);
$this->assertEquals('SELECT * FROM tbl WHERE username IS NOT NULL', QueryAssembler::stringify($statement));
$where->addPredicate((new LikePredicate())->setField('name')->setExpression(StartsWithExpression::create('Joh')));
$this->assertEquals('SELECT * FROM tbl ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%"', QueryAssembler::stringify($statement));
$orderBy = new OrderByClause();
$statement->addClause($orderBy);
$orderBy->addField((new FieldExpression())->setField('user_id'));
$this->assertEquals('SELECT * FROM tbl ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%" ' . 'ORDER BY user_id', QueryAssembler::stringify($statement));
$orderBy->addField((new FieldExpression())->setField('age'), 'DESC');
$this->assertEquals('SELECT * FROM tbl ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%" ' . 'ORDER BY user_id, age DESC', QueryAssembler::stringify($statement));
$groupBy = new GroupByClause();
$statement->addClause($groupBy);
$groupBy->addField((new FieldExpression())->setField('role'));
$this->assertEquals('SELECT * FROM tbl ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%" ' . 'GROUP BY role ' . 'ORDER BY user_id, age DESC', QueryAssembler::stringify($statement));
$having = new HavingClause();
$statement->addClause($having);
$having->addPredicate((new LessThanPredicate())->setField('tasks')->setExpression((new NumericExpression())->setValue(4)));
$this->assertEquals('SELECT * FROM tbl ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%" ' . 'GROUP BY role ' . 'HAVING tasks < 4 ' . 'ORDER BY user_id, age DESC', QueryAssembler::stringify($statement));
$limit = new LimitClause();
$limit->setLimit(10);
$limit->setOffset(20);
$statement->addClause($limit);
$this->assertEquals('SELECT * FROM tbl ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%" ' . 'GROUP BY role ' . 'HAVING tasks < 4 ' . 'ORDER BY user_id, age DESC ' . 'LIMIT 20,10', QueryAssembler::stringify($statement));
}
