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