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

Hi.

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

		$this->ensureTable($table);

		if($criteria->group){

			$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 {

			$criteria->select='COUNT(*)';

		}


		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? :)

up.

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

	{

		$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?

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,

"SELECT COUNT(1)"

is faster than

"SELECT COUNT(*)"

regards!!

:)

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:

http://code.google.com/p/yii/issues/detail?id=1199&q=count&colspec=ID%20Type%20Status%20Priority%20Milestone%20Owner%20Stars%20Summary