public static function selectDistinct(...$expressions)
{
$select = new SelectClause();
$select->setDistinct(true);
$select->addFields($expressions);
$statement = static::_getQueryStatement();
$statement->addClause($select);
return $statement;
}
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));
}
/**
* Find all distinct values of a property in the collection
*
* @param $property
*
* @return array
*/
public function distinct($property)
{
if ($this->isEmpty()) {
$select = new SelectClause();
$select->setDistinct(true);
$select->addField($property);
$originalClause = $this->_query->getClause('SELECT');
$this->_query->addClause($select);
$results = $this->_getDataStore()->getData($this->_query);
$this->_query->addClause($originalClause);
if (empty($results)) {
return [];
}
return Arrays::ipull($results, $property);
}
return parent::distinct($property);
}
public function testGettersAndSetters()
{
$clause = new SelectClause();
$field = new FieldSelectExpression();
$now = new NowSelectExpression();
$this->assertFalse($clause->hasExpressions());
$clause->addExpression($field);
$this->assertTrue($clause->hasExpressions());
$this->assertSame([$field], $clause->getExpressions());
$clause->clearExpressions();
$clause->setExpressions([$field, $now]);
$this->assertTrue($clause->hasExpressions());
$clause->clearExpressions();
$this->assertFalse($clause->hasExpressions());
$this->setExpectedException("InvalidArgumentException");
$clause->setExpressions([$field, $now, 'abc']);
}
public function assembleSelectClause(SelectClause $clause)
{
$return = $clause->getAction() . ($clause->isDistinct() ? ' DISTINCT' : '');
if (!$clause->hasExpressions()) {
return $return . ' *';
}
return $return . ' ' . implode(', ', $this->assembleSegments($clause->getExpressions()));
}
