CActiveRecord->count don't works with having

Hi guys

I found a bug in CACtiveRecord->count().

It return the wrong result if invoked with the having condition.

There is an issue opened for this bug (1244), in wich there is a small test.

I resolved by changing the code of CDbCommandBuilder at line 91:




	/**

	 * Creates a COUNT(*) command for a single table.

	 * @param mixed the table schema ({@link CDbTableSchema}) or the table name (string).

	 * @param CDbCriteria the query criteria

	 * @param string the alias name of the primary table. Defaults to 't'.

	 * @return CDbCommand query command.

	 */

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

	{


		$this->ensureTable($table);

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

		if($criteria->alias!='')

			$alias=$criteria->alias;

		$alias=$this->_schema->quoteTableName($alias);

		$sql=($criteria->distinct ? 'SELECT DISTINCT':'SELECT')." {$select} FROM {$table->rawName} $alias";

		$sql=$this->applyJoin($sql,$criteria->join);

		$sql=$this->applyCondition($sql,$criteria->condition);

		$sql=$this->applyGroup($sql,$criteria->group);

		$sql=$this->applyHaving($sql,$criteria->having);

		

		$sql="SELECT COUNT(*) FROM ($sql) sq";

		$command=$this->_connection->createCommand($sql);

		$this->bindValues($command,$criteria->params);

		return $command;

	}






This code works fine in any situation.

Can you tell me if is correct and if you will patch this on the framework?

Hallo to all Yii develop team.

That is not a banal bump post. Is a scared and nostalgetic call. I am an old user of this framework, much older than my forum’s user.

I posted for long with the nick of ilzale, my ex colleague of work. I remember the times when Yii was young and bug reports were read, sometime solved, sometime declared invalid, sometime leaved as they were, but at least all were at least READ and to all post in bug reports there were some answers.

In this ancent and young time was even impossible to immagine a bug report about CActiveRecord that were ignored for a week, and a suggestion of solution leaved without even a comment, an issue covered of dust.

How looks far and happy the times of this, this or this this topic.

I can understand that now the framework grows up, that now there are much more user and that is May and everibody is really busy in May, but this silence is really scaring.

Where are you, Qiang? I worked in PRADO years ago, and I followed you in this new adventure. What are you doing now? Are you writing a new one framework? Or are you just in vacation?

You are our god, Qiang, give us a Sign!

I wrote a patch for this bug. Yii is really nice and extensible, and that is nice because allows developer to fix bug without touch the core framework.

If anyone needs this patch can pm me and I will be glad to send him, but I don’t want to post it in the forum, because I still belive that bux fixing should follow more standard paths.

zaccaria

You are right about framework growth but not right about reports are not read: http://code.google.com/p/yii/updates/list

I’ve assigned this one to myself. Will check your solution.

Thank you very much for your reply.

Maybe I should not be so rude in my post, sorry for that. Anyone is busy and we are receiving this nice framework for free, so please excuse my hard words.

zaccaria

Your code does not break any existing unit tests but as I know we don’t have too much tests for count().

If you have some time to write tests for new functionality (having for count) it would be really great and will speed things up a lot.

There are some news about solutions?

I noticed the

Ref: - Enh #1244: CActiveRecord::count() now respects GROUP-BY and HAVING settings (Qiang)

but it doesn’t solve this issue.

If you try my example*, it still return the wrong result.

Sorry for test cases, but I have never tried creating tests… maybe I should! :)

In the issue there is a zip with an example of the bug. Sorry but I cannot do anithing better than this!

zaccaria

This one was partly fixed. Status updates and discussions are here: http://code.google.com/p/yii/issues/detail?id=1244

Hi guys.

In yii 1.5 the bug is still active, using the example provided here in the test.zip attached, and is still resolvable with the example of code provided in the issue.

Will be fixed?

This bug has been fixed.

Thank to the Yii staff for the work!

I think it would be nice if Yii source code repository has something like a "pull request", where patches from anyone can be quickly "pulled" and tested and applied to the trunk, without the troublesome of going through file uploads, forums replies and copy codes to here and there manually etc. Like what they have in Github and bitbucket. It makes things work faster.

awesome!

I found this bug still exists for relational queries.

Added a comment about this to the issue. I had no time yet to dig into the code but maybe someone involved in the fix can tell, if a similar fix can be applied to CJoinElement? This line is probably causing the bug there:

CActiveFinder:756


$query->orders=$query->groups=$query->havings=array();

I confirm that this bug still exists.

CActiveFinder->count() will not give a proper count value when a COUNT() function already exists in the select.

My dbcriteria looks like this:

$criteria->select = "COUNT(CASE WHEN b.status=0 THEN 1 ELSE NULL END) AS total, t.*"

$criteria->group = ‘t.id’

I also confirm that this still exists (and it seems the issue is still open, but not assigned, on GitHub).

For me, I am passing criteria to a CActiveDataProvider for a relational query using several withs, on one of which I do a HAVING with a COUNT criteria, and I group by the CActiveDataProvider model’s id.

The actual rows returned are fine, but the count is incorrect, and from looking at the SQL queries generated, the count is simply stripping my HAVING clause out of the query.

A fix for this would be much appreciated.

Same problem with COUNT and HAVING on a relational query




$criteria->with = array('userCategories'=>array('alias'=>'UC','together'=>true));

$criteria->having = " count(CASE WHEN UC.idCategory=2 THEN 1 END) > 0 "



It was fixed recently. Would be great if you’ll test it https://github.com/yiisoft/yii/pull/2167