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