SELECT COUNT(DISTINCT(requestId)) FROM `offer` `t` WHERE ((t.cid=17) AND (t.status<>'deleted')) AND ((marker='unread') AND ((created>=:start) AND (t.created<=:stop))) ORDER BY created DESC
To get info for pagination. And right now i can’t do it with AR, cause COUNT(DISTINCT(requestId)) is always replaced with COUNT(*).
Probably the right solution is using ‘select’ property of DBCriteria for count, but i’m not sure about hidden problems that can occur.
IMHO the best solution (for mysql at least) would be using SQL_CALC_FOUND_ROWS in query and SELECT FOUND_ROWS() after that to obtain exact row count for that query. It’s much faster and it’s reliable
Well, i just looked at trunk and found next "solution":
public function createCountCommand($table,$criteria,$alias='t')
{
$this->ensureTable($table);
if($criteria->alias!='')
$alias=$criteria->alias;
$alias=$this->_schema->quoteTableName($alias);
if($criteria->distinct)
{
if(is_array($table->primaryKey))
{
$pk=array();
foreach($table->primaryKey as $key)
$pk[]=$alias.'.'.$key;
$pk=implode(', ',$pk);
}
else
$pk=$alias.'.'.$table->primaryKey;
$sql="SELECT COUNT(DISTINCT $pk)";
}
else
$sql="SELECT COUNT(*)";
$sql.=" FROM {$table->rawName} $alias";
$sql=$this->applyJoin($sql,$criteria->join);
$sql=$this->applyCondition($sql,$criteria->condition);
$command=$this->_connection->createCommand($sql);
$this->bindValues($command,$criteria->params);
return $command;
}
Well, it’s not correct solution, because in our case ‘requestId’ is not PrimaryKey. It’s ForeignKey! So as i understand, that ‘fix’ will not solve existing problems. Why so many restrictions?
You could open a ticket as a reminder for the dev team. I understand that these kind of changes need careful planning instead of doing a rushed implementation that makes problems later.