public function testAssemble() { $statement = QueryBuilder::update('tbl', ['field1' => 'value1']); $this->assertEquals('UPDATE tbl SET field1 = "value1"', QueryAssembler::stringify($statement)); $statement->where(['NOT' => ['username' => null]]); $this->assertEquals('UPDATE tbl SET field1 = "value1" WHERE username IS NOT NULL', QueryAssembler::stringify($statement)); $statement->set('username', 'john'); $statement->andWhere((new LikePredicate())->setField('name')->setExpression(StartsWithExpression::create('Joh'))); $this->assertEquals('UPDATE tbl SET field1 = "value1", username = "******" ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%"', QueryAssembler::stringify($statement)); $statement->set('bob', null); $this->assertEquals('UPDATE tbl SET field1 = "value1", username = "******", bob = NULL ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%"', QueryAssembler::stringify($statement)); $statement->orderBy(['username' => 'asc']); $this->assertEquals('UPDATE tbl SET field1 = "value1", username = "******", bob = NULL ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%" ORDER BY username ASC', QueryAssembler::stringify($statement)); $statement->orderBy(['username' => 'desc', 'field1' => 'asc']); $this->assertEquals('UPDATE tbl SET field1 = "value1", username = "******", bob = NULL ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%" ' . 'ORDER BY username DESC, field1 ASC', QueryAssembler::stringify($statement)); $statement->limit(2); $this->assertEquals('UPDATE tbl SET field1 = "value1", username = "******", bob = NULL ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%" ' . 'ORDER BY username DESC, field1 ASC LIMIT 2', QueryAssembler::stringify($statement)); }
public function testAssemble() { $predicate = new NotLikePredicate(); $predicate->setField('field'); $this->assertEquals('field NOT LIKE NULL', QueryAssembler::stringify($predicate)); $predicate->setExpression(CustomLikeExpression::create(1)); $this->assertEquals('field NOT LIKE 1', QueryAssembler::stringify($predicate)); $predicate->setExpression(CustomLikeExpression::create('1')); $this->assertEquals('field NOT LIKE "1"', QueryAssembler::stringify($predicate)); $predicate->setExpression(CustomLikeExpression::create('abc')); $this->assertEquals('field NOT LIKE "abc"', QueryAssembler::stringify($predicate)); $predicate->setExpression(EndsWithExpression::create('abc')); $this->assertEquals('field NOT LIKE "%abc"', QueryAssembler::stringify($predicate)); $predicate->setExpression(ContainsExpression::create('abc')); $this->assertEquals('field NOT LIKE "%abc%"', QueryAssembler::stringify($predicate)); $predicate->setExpression(StartsWithExpression::create('abc')); $this->assertEquals('field NOT LIKE "abc%"', QueryAssembler::stringify($predicate)); $predicate->setExpression(CustomLikeExpression::create('a%bc')); $this->assertEquals('field NOT LIKE "a%bc"', QueryAssembler::stringify($predicate)); }
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)); }