Why count() of Active Record has so odd behaviour?


Well, i need next SQL:

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.

I found that ‘patch’ here

	public function createCountCommand($table,$criteria) {



			$select=is_array($criteria->group) ? implode(', ',$criteria->group) : $criteria->group;

			$criteria->select = 'COUNT(DISTINCT (' . $select . '))';

			$criteria->distinct = false; // ADDED

		} else if ($criteria->distinct) {

			$select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select;

			$criteria->select = 'COUNT(DISTINCT(' . $select . '))';

			$criteria->distinct = false; // ADDED

		} else {



		return $this->createFindCommand($table, $criteria);


So my question is - what problems can occur with that solution? Looks like there is no real problem, but who knows?!

Qiang and rest team…what is your answer? :)


urgent for us.

maybe we should make patch and post it as issue in code.google?

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

Should be fixed in SVN now.

Well, i just looked at trunk and found next "solution":

public function createCountCommand($table,$criteria,$alias='t')











				foreach($table->primaryKey as $key)


				$pk=implode(', ',$pk);







			$sql="SELECT COUNT(*)";

		$sql.=" FROM {$table->rawName} $alias";





		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?

It’s not really a restriction, but rather a question of how to create a more general solution. So what’s your proposal?

I thought about optionally setting the DISTINCT column name in distinct:

is_bool($criteria->distinct) : Current behavoir

is_string($criteria->distinct): Use this as column name

Hmmm…looks like that solution would be more universal! What core-team will say? :)

hey, guys,


is faster than




So, quang, will you fix it?

I hoped that some fixes will be at 1.1.2, but looks like not. So is it planned to fix or just trashed?

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.

Ok, looks like dev-team is reading forum time from time, that’s why i opened ticket: