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