ActiveRecord: STAT => HAVING => extremly slow

I defined a STAT relation like this:




public function relations()

{

	return array(

		'items'=>array(self::MANY_MANY, 'Item', 'Item_Keyword(itemId, keywordId)'),

		'itemsCount'=>array(self::STAT, 'Item', 'Item_Keyword(itemId, keywordId)'),

	);

}



Then I apply this relation to an instance of this model, a "Keyword" read from the database.




echo $data->itemsCount; // $data is a Keyword



It works as planned, except that loading a page takes several seconds. The pages are then cached, but the first load is awfully slow. The log shows that the SQL query uses HAVING. If I replace this by a WHERE, then MySQL answers in a split second.

Is this normal? Is there an easy way to force STAT to use WHERE? Or should I drop STAT for this use?

Sorry I didn’t see there was already a ticket for this. It is closed, so I missed it in my first search.

I suggest a patch that replaces the HAVING by a WHERE in the sql query. On my example database, the queries that took ~ 7 s now take 0.001 s. The unit tests are unchanged by this patch, so I consider it harmless.




Index: yii/framework/db/ar/CActiveFinder.php

===================================================================

--- yii/framework/db/ar/CActiveFinder.php	(révision 1935)

+++ yii/framework/db/ar/CActiveFinder.php	(copie de travail)

@@ -1495,17 +1495,16 @@

 			}

 		}

 

-		$where=empty($relation->condition)?'' : ' WHERE ('.$relation->condition.')';

+		$where=empty($relation->condition)?'' : ' AND ('.$relation->condition.')';

 		$group=empty($relation->group)?'' : ', '.$relation->group;

-		$having=empty($relation->having)?'' : ' AND ('.$relation->having.')';

+		$having=empty($relation->having)?'' : ' HAVING ('.$relation->having.')';

 		$order=empty($relation->order)?'' : ' ORDER BY '.$relation->order;

 

 		$sql='SELECT '.$this->relation->select.' AS '.$schema->quoteColumnName('s').', '.implode(', ',$cols)

 			.' FROM '.$table->rawName.' INNER JOIN '.$joinTable->rawName

 			.' ON ('.implode(') AND (',$joinCondition).')'

-			.$where

+			.' WHERE ('.$builder->createInCondition($joinTable,$map,$keys).')'.$where

 			.' GROUP BY '.implode(', ',array_keys($cols)).$group

-			.' HAVING ('.$builder->createInCondition($joinTable,$map,$keys).')'

 			.$having.$order;

 

 		$command=$builder->getDbConnection()->createCommand($sql);