I'm having tables structure A (1…many) -> B (1…many) -> C.
I have following model relations:
'bs' => array(self::HAS_MANY, 'B', 'id_a'),
'cs' => array(self::HAS_MANY, 'C', 'id_b'), 'statRel0' => array(self::STAT, 'C', 'id_b', 'condition' => 'field=0'),
In my code I have the following:
$criteria = new CDbCriteria; $criteria->order = "id_a"; $criteria->condition = 'some_field=100'; $AList = A::model() ->with('bs', 'bs.statRel0') ->findAll($criteria);
This produces for me 3 queries:
SELECT ... FROM `table_a` WHERE (some_field=100) ORDER BY id_a SELECT ... FROM `table_a` LEFT OUTER JOIN `table_b` t1 ON (t1.`id_a`=`table_a`.`id_a`) WHERE (`table_a`.`id_a`=200) SELECT `id_b` AS `c`, COUNT(*) AS `s` FROM `table_c` WHERE (field=0) GROUP BY `id_b` HAVING `table_c`.`id_b` IN (301, 302, 303)
The problem is in last query's HAVING statement. Is it does not use index on id_b field this is significantly slows down the query. Why Yii uses HAVING in this query and is there any workaround to move this into WHERE clause?
Thanks for help.