/**
* @return SelectQuery
**/
public function makeCountQuery()
{
$query = $this->makeFetchQuery();
if ($query->isDistinct()) {
$countFunction = SQLFunction::create('count', DBField::create($this->container->getDao()->getIdName(), $this->container->getDao()->getTable()))->setAggregateDistinct();
$query->unDistinct();
} else {
$countFunction = SQLFunction::create('count', DBValue::create('*'));
}
return $query->dropFields()->dropOrder()->dropLimit()->get($countFunction->setAlias('count'));
}
public function testBind()
{
$user = TestUser::create()->setId(1);
$bindingsList = array(array(1 => 1.123), array(1 => -1), array(1 => 'test'), array(1 => $user), array(1 => SQLFunction::create('rand')));
foreach ($bindingsList as $bindings) {
$value = $bindings[1];
if ($value instanceof Identifiable) {
$value = $value->getId();
}
$this->assertCriteria('$1 from TestUser', Criteria::create(TestUser::dao())->setProjection(Projection::property($value)), $bindings)->assertCriteria('count($1) from TestUser', Criteria::create(TestUser::dao())->setProjection(Projection::count($value)), $bindings)->assertCriteria('from TestUser where id = $1', Criteria::create(TestUser::dao())->add(Expression::eq('id', $value)), $bindings);
// in 'in' expression
if (is_scalar($value)) {
$this->assertCriteria('from TestUser where id in (1, $1)', Criteria::create(TestUser::dao())->add(Expression::in('id', array(1, $value))), $bindings);
}
$this->assertCriteria('from TestUser order by $1', Criteria::create(TestUser::dao())->addOrder(OrderBy::create($value)), $bindings)->assertCriteria('from TestUser having id = $1', Criteria::create(TestUser::dao())->setProjection(Projection::having(Expression::eq('id', $value))), $bindings)->assertCriteria('from TestUser group by id = $1', Criteria::create(TestUser::dao())->setProjection(Projection::group(Expression::eq('id', $value))), $bindings);
if (is_integer($value) && $value >= 0) {
$this->assertCriteria('from TestUser limit $1', Criteria::create(TestUser::dao())->setLimit($value), $bindings)->assertCriteria('from TestUser offset $1', Criteria::create(TestUser::dao())->setOffset($value), $bindings);
}
}
}
public function testSelectGet()
{
$dialect = $this->getDbByType('PgSQL')->getDialect();
$query = OSQL::select()->from('test_table')->get(DBField::create('field1', 'test_table'), 'alias1')->get(DBField::create('field2', 'test_table'))->get('field3', 'alias3')->get('field4')->get(SQLFunction::create('count', DBField::create('field5', 'test_table'))->setAggregateDistinct()->setAlias('alias5'))->get(SQLFunction::create('substring', BinaryExpression::create(DBField::create('field6', 'test_table'), DBValue::create('a..b'), 'from')->noBrackets()));
$this->assertEquals($query->toDialectString($dialect), 'SELECT ' . '"test_table"."field1" AS "alias1", ' . '"test_table"."field2", ' . '"test_table"."field3" AS "alias3", ' . '"test_table"."field4", ' . 'count(DISTINCT "test_table"."field5") AS "alias5", ' . 'substring("test_table"."field6" from \'a..b\') ' . 'FROM "test_table"');
}
public function getQueryResult(SelectQuery $query, $expires = Cache::DO_NOT_CACHE)
{
if ($expires !== Cache::DO_NOT_CACHE && ($list = $this->getCachedByQuery($query))) {
return $list;
} else {
$list = $this->fetchList($query);
$count = clone $query;
$count = DBPool::getByDao($this->dao)->queryRow($count->dropFields()->dropOrder()->limit(null, null)->get(SQLFunction::create('COUNT', '*')->setAlias('count')));
return $this->cacheByQuery($query, $list ? QueryResult::create()->setList($list)->setCount($count['count'])->setQuery($query) : QueryResult::create(), $expires);
}
}
public function testSelectGet()
{
$dialect = PostgresDialect::me();
$query = OSQL::select()->from('test_table')->get(DBField::create('field1', 'test_table'), 'alias1')->get(DBField::create('field2', 'test_table'))->get('field3', 'alias3')->get('field4')->get(SQLFunction::create('count', DBField::create('field5', 'test_table'))->setAlias('alias5'));
$this->assertEquals($query->toDialectString($dialect), 'SELECT ' . '"test_table"."field1" AS "alias1", ' . '"test_table"."field2", ' . '"test_table"."field3" AS "alias3", ' . '"test_table"."field4", ' . 'count("test_table"."field5") AS "alias5" ' . 'FROM "test_table"');
}
/**
* @return InsertOrUpdateQuery
**/
protected function addReturning(InsertOrUpdateQuery $query)
{
$query->returning(DBField::create('field1', 'test_table'), 'alias1')->returning('field2')->returning(SQLFunction::create('count', DBField::create('field5', 'test_table'))->setAlias('alias5'))->returning(OSQL::select()->from('test_table1')->setName('foo1')->get('id'));
return $query;
}
/**
* @return JoinCapableQuery
**/
public function process(Criteria $criteria, JoinCapableQuery $query)
{
Assert::isNotNull($this->property);
return $query->get(SQLFunction::create($this->getFunctionName(), $criteria->getDao()->guessAtom($this->property, $query))->setAlias($this->alias));
}
public function testSqlFunction()
{
$criteria = Criteria::create(TestCity::dao())->addProjection(Projection::property(SQLFunction::create('count', SQLFunction::create('substring', BinaryExpression::create('name', BinaryExpression::create(DBValue::create('M....w'), DBValue::create('#'), 'for')->noBrackets(), 'from')->noBrackets()))->setAggregateDistinct()->setAlias('my_alias')));
$this->assertEquals($criteria->toDialectString(ImaginaryDialect::me()), 'SELECT count(DISTINCT substring(custom_table.name from M....w for #)) AS my_alias FROM custom_table');
}
/**
* @return SQLFunction
**/
protected function getFunction(Criteria $criteria, JoinCapableQuery $query)
{
Assert::isNotNull($this->property);
return SQLFunction::create('count', $this->property ? $criteria->getDao()->guessAtom($this->property, $query) : $criteria->getDao()->getIdName());
}
/**
* @return SelectQuery
**/
public function makeTotalCountQuery()
{
return OSQL::select()->get(SQLFunction::create('count', DBValue::create('*')))->from($this->getTable());
}
public function toDialectString(Dialect $dialect)
{
return '(' . $dialect->toFieldString(SQLFunction::create('lower', $this->left)) . ' = ' . $dialect->toValueString(is_string($this->right) ? mb_strtolower($this->right) : SQLFunction::create('lower', $this->right)) . ')';
}
/**
* @return DialectString
*
* FIXME: DBI-result, method works only for PostgreSQL.
* Research how to generate series of values in MySQL and implement
* this.
**/
private function getSeriesGenerator($start, $stop, $step = null)
{
if (!$step) {
$result = SQLFunction::create('generate_series', DBValue::create($start)->castTo(DataType::create(DataType::INTEGER)->getName()), DBValue::create($stop)->castTo(DataType::create(DataType::INTEGER)->getName()));
} else {
$result = SQLFunction::create('generate_series', DBValue::create($start)->castTo(DataType::create(DataType::INTEGER)->getName()), DBValue::create($stop)->castTo(DataType::create(DataType::INTEGER)->getName()), DBValue::create($step)->castTo(DataType::create(DataType::INTEGER)->getName()));
}
return $result;
}